mysql two unique columns, values in one col to be considered duplicate if inserted in the other col

Go To


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:

ADD UNIQUE myunique ( col1 ,col2 ) 


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

2012-04-04 07:25
by Alqin


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...

2012-04-04 07:29
by Konerak


Your code

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?

2012-04-04 07:30
by Ankit Sharma
Also, your questions could be comments for the questions. No need to include them here - ragingasiancoder 2016-07-01 13:36