CODEWITHSUNDEEP
phpmysqlsqldatabaseindexing
Sergio La Rosa
Sergio La Rosa

Reputation: 495

MySQL and UNIQUE columns: how to avoid duplicates?

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?

Upvotes: 0

Views: 73

Answers (2)

bobflux
bobflux

Reputation: 11591

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.

Upvotes: 1

fancyPants
fancyPants

Reputation: 51928

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.

Upvotes: 1

Related Questions