Search code examples
mysqldatabasekeyunique-key

MySQL two-column unique key

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.

Solution

  • You need composite unique index.

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