Prevent a combination of items from being inserted twice in MySQL?

To prevent a combination of items from being inserted twice, alter the table and set UNIQUE for the column as shown in the below syntax −

alter table yourTableName add constraint yourConstraintName unique(yourColumnName1,yourColumnName2,....N);

Let us first create a table −

mysql> create table DemoTable
(
   Value1 int,
   Value2 int
);
Query OK, 0 rows affected (0.47 sec)

Here is the query to prevent a combination of items from being inserted twice −

mysql> alter table DemoTable add constraint Value1_Value2_ConstraintKey unique(Value1,Value2);
Query OK, 0 rows affected (0.80 sec)
Records : 0 Duplicates : 0 Warnings : 0

Insert some records in the table using insert command. Here, when we try to insert duplicate combination, an error “Duplicate entry” is visible and the duplicate insertion won’t work −

mysql> insert into DemoTable values(10,10);
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable values(10,20);
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable values(10,10);
ERROR 1062 (23000) : Duplicate entry '10-10' for key 'Value1_Value2_ConstraintKey'
mysql> insert into DemoTable values(20,10);
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable values(20,20);
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable values(20,10);
ERROR 1062 (23000) : Duplicate entry '20-10' for key 'Value1_Value2_ConstraintKey'

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+--------+--------+
| Value1 | Value2 |
+--------+--------+
|     10 |     10 |
|     10 |     20 |
|     20 |     10 |
|     20 |     20 |
+--------+--------+
4 rows in set (0.00 sec)