Is it possible in mysql to make two columns unique, but a value inserted in one column to be considered duplicate if is found in the other column?

I tried this code sugested, it doesn't work:

ALTER TABLE test 
ADD UNIQUE myunique ( col1 ,col2 ) 

EDIT:

Thanks for the answers, I'll find others solution, but avoid the one with the trigger

• mysql
• sql
• unique

No, there is no way to do that with native MySQL Constraints.

Your UNIQUE ( col1 ,col2 ) will consider "combinations" of both columns to be unique. So (a,a) is allowed, unless there already is another (a,a). That seems not to be what you want.

You can try using a trigger, but that's rather clumsy.

Why do you need two columns? If the columns represent the same item (since duplicates are not allowed), wouldn't one column not be better? You could add a second column for a "rank" if there are other differences...

Your code

 ALTER TABLE test 
ADD UNIQUE myunique ( col1 ,col2 ) 

does not work because it makes a composite, unique index, which makes the duplicate entry in col1 and col2 separate. As you said you don't need this.

I haven't tried it yet, but have you tried a trigger for your purpose?