Unique constraint on two columns regardless of order

sql-server, unique-constraint

Solution

You could maybe create computed column, something like:

CREATE TABLE [Car] 
(
   CarID int NOT NULL PRIMARY KEY IDENTITY(1,1),
   FirstColorID int, --FOREIGN KEY REFERENCES Colors(ColorID),
   SecondColorID int, --FOREIGN KEY REFERENCES Colors(ColorID),
   xColor As Cast(Case When FirstColorID > SecondColorID Then FirstColorID Else SecondColorID End as varChar) + ',' + 
        Cast(Case When FirstColorID <= SecondColorID Then SecondColorID Else FirstColorID End as varChar),
   UNIQUE(xColor)
)

UPDATE (You should test it before, i did just very quick testing)

Idea:

Integer is 4 byts. If i put 2 integers together- i get 8 bytes. If i order them- i get unique BigInt value (8 bytes).

So what i do:

- I make sure they are in correct order

- I shift bytes of 32 bits to left (so just by multiplying 4294967296 i get what i want)

- I make logical OR- so i get 8 byte BigInt value, that should always be unique!

So:

CREATE TABLE [Car] 
(
   CarID int NOT NULL PRIMARY KEY IDENTITY(1,1),
   FirstColorID int, --FOREIGN KEY REFERENCES Colors(ColorID),
   SecondColorID int, --FOREIGN KEY REFERENCES Colors(ColorID),
   xColor As 
       Case When FirstColorID > SecondColorID Then 
            Cast(FirstColorID as BigInt) * Cast(4294967296 as BigInt) | Cast(SecondColorID as BigInt)
        Else 
            Cast(SecondColorID as BigInt) * Cast(4294967296 as BigInt) |  Cast(FirstColorID as BigInt)
        End
  UNIQUE(xColor)
)

Problem

I have the following table definition: ``` CREATE TABLE [Car] ( CarID int NOT NULL PRIMARY KEY IDENTITY(1,1), FirstColorID int FOREIGN KEY REFERENCES Colors(ColorID), SecondColorID int FOREIGN KEY REFERENCES Colors(ColorID), UNIQUE(FirstColorID, SecondColorID) ) ``` I want the two Color columns to be unique, regardless of the combination they appear in. E.g. attemping: ``` INSERT INTO Car (FirstColorID, SecondColorID) VALUES (1, 2); --should succeed ``` but then trying the same after that first record exists with the colors reversed should fail: ``` INSERT INTO Car (FirstColorID, SecondColorID) VALUES (2, 1); --should violate constraint/check ``` I have this problem solved using high-level code, but I'd rather the constraint be enforced directly in the database, preferably without involving things like triggers. Thanks.

Original source