7

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.

Improve this question
3
  • 1
    what about concatenating the two columns to a third column and make that the unique key? Commented Dec 22, 2017 at 13:51
  • 2
    You can place a unique constraint on multiple keys, e.g. ALTER TABLE yourtable ADD UNIQUE unique_index(col1, col2); Commented Dec 22, 2017 at 13:52
  • 1
    You want a two field unique key as @GiorgosBetsos says Commented Dec 22, 2017 at 13:53

2 Answers 2

Reset to default
14

You need composite unique index.

ALTER TABLE tablename ADD UNIQUE KEY `uidx` (`col1`, `col2`);
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

6

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.