MySQL and UNIQUE columns: how to avoid duplicates?

Question

I have a table with the following column:

(id, col_1, col_2)

where "id" is the primary key (auto-inc).

I would like to make col_1 and col_2 a "unique combo".

For example, if I insert the data...

(.., col_1, col_2 ) = (.., 'A', 'B')

...Next, I shouldn't be allowed to insert the following:

(.., col_1, col_2 ) = (.., 'A', 'B')
(.., col_1, col_2 ) = (.., 'B', 'A') #because it already exists, in another order:('A','B')

I successfully blocked the first case with the use of UNIQUE(col_1, col_2)...

But it seems that the "reverse combo" (.., 'B', 'A') is permitted.

How can I stop this?

you can make a trigger on insert and on update and check if such combination already exists — Petr Jirouš, Mar 29 '17 at 13:16
@fancyPants I don't think it works in this condition. Because they are different (A-B) != (B-A) — Alex Slipknot, Mar 29 '17 at 13:17
[This question has been asked on DBA.SE as well.](//dba.stackexchange.com/questions/14109/two-sided-unique-index-for-two-columns) — Ivar, Mar 29 '17 at 13:25
Possible duplicate of [How To Create A 'Two-Sided' Unique Index On Two Fields?](http://stackoverflow.com/questions/229447/how-to-create-a-two-sided-unique-index-on-two-fields) — Ivar, Mar 29 '17 at 13:27
Add two unique "combo" keys? `.. add unique key(a,b), unique key(b,a)` — Lexx918, Mar 29 '17 at 13:43

score 1 · Accepted Answer · answered Mar 29 '17 at 13:21

1

After trying it out it really isn't as easy as creating another unique index.
This seems to be an odd flaw in MySQL. A workaround though can be to always store the lesser value in col_1 and the greater value in col_2 (when you don't like triggers, that is):

INSERT INTO your_table (col_1, col_2) VALUES (LEAST('A', 'B'), GREATEST('A', 'B'));

You can find the documentation to these functions here.

answered Mar 29 '17 at 13:21

fancyPants

50,732
33
89
96

I'll take this as temp solution. Thanks. – Sergio La Rosa Mar 29 '17 at 15:26
Seems like a permanent solution, along with `UNIQUE(col_1, col_2)`. – Rick James Mar 31 '17 at 18:42

score 1 · Answer 2 · edited May 23 '17 at 12:02

As said by fancyPants, the solution is to ensure one column is always lower than the other. MySQL does not support CHECK constraints, but you can enforce this with a trigger, which will allow you to catch application bugs:

Can a MySQL trigger simulate a CHECK constraint?

Beware: depending on MySQL version, raising an error with your trigger will use different methods.

On newer MySQL versions, you can also create virtual columns and index them. Perhaps you could create a UNIQUE(least(a,b), greatest(a,b))... but it would be kinda clunky.

Also, knowing that a<=b is a constraint will speed up search. If you need to search for either (x,y) or (y,x) then you know that the only one in your table will be the one with proper order, which simplifies your WHERE and makes index use much better.

I would avoid the trigger solution... but i'll try this too. Thanks. — Sergio La Rosa, Mar 29 '17 at 15:27

MySQL and UNIQUE columns: how to avoid duplicates?

2 Answers2