Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sql unique constraint on a 2 columns combination

How can you create a unique constraint on a combination of two values in two columns.

meaning

column1  column2 
   2        1 

looking for the constraint to disallow

column1  column2 
   1        2 
like image 220
cechode Avatar asked Feb 21 '23 19:02

cechode


1 Answers

If your database allows expressions in an index you can do something like this (ANSI SQL):

CREATE UNIQUE INDEX on your_table (least(column1, column2)
                              , greatest(column1, column2));

Note this is a unique index not a unique constraint. The only difference for most DBMS is that you can't have a unique index as the target of a foreign key, but otherwise they serve the same purpose.

If your DBMS does not have least() or greatest() you can replace that using a CASE expression:

create unique index on your_table 
  (case column1 < column2 then column1 else column2 end, 
   case column2 > column1 then column2 else column1 end));
like image 167
a_horse_with_no_name Avatar answered Feb 28 '23 12:02

a_horse_with_no_name