MySQL two-column unique key

Question

I know that i can make two columns unique keys, but that's not exactly what i want.

I want that for example if col1='1', col2='2' then there can't be another row with col1='1', col2='2', but totally possible to do the following:

+--------+--------+
|  col1  |  col2  |
+--------+--------+
|    1   |    1   |
|    1   |    2   |
|    2   |    1   |
|    2   |    2   |
+--------+--------+

while this is impossible:

+--------+--------+
|  col1  |  col2  |
+--------+--------+
|    1   |    1   |
|    1   |    1   |
+--------+--------+

Making both unique keys is not an option as in col1='1', col2='1' and col1='1', col2='2' col1 is the same and that's not allowed if both are unique keys.

what about concatenating the two columns to a third column and make that the unique key? — p130ter, Dec 22 '17 at 13:51
You can place a unique constraint on multiple keys, e.g. `ALTER TABLE yourtable ADD UNIQUE `unique_index`(`col1`, `col2`);` — Giorgos Betsos, Dec 22 '17 at 13:52

score 13 · Accepted Answer · answered Dec 22 '17 at 13:57

13

You need composite unique index.

ALTER TABLE tablename ADD UNIQUE KEY `uidx` (`col1`, `col2`);

answered Dec 22 '17 at 13:57

Samir Selia

7,007
2
11
30

score 5 · Answer 2 · edited Nov 20 '19 at 17:08

5

You just need to declare a unique index between the two columns col1, and col2:

CREATE TABLE Table1
(
  `col1` int, 
  `col2` int,
   UNIQUE `unique_index`(`col1`, `col2`)
);

If you try to insert 1, 1, into col1 and col2, you will get the following error:

Duplicate entry '1-1' for key 'unique_index'

You can try it yourself here.

edited Nov 20 '19 at 17:08

Samir Selia

7,007
2
11
30

answered Dec 22 '17 at 13:53

MySQL two-column unique key

2 Answers2