CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2
sudhansu63
sudhansu63

Reputation: 6180

How to give a unique constraint to a combination of columns in SQL irrespective of Order

I have a two tables. First One is the product table that hold the Data and the second one holds the Parent Child relationship.

ProductTable
===================
PkId Manufacturer Model ...
1      A            A1
2      B            B1
3      C            C1

JoinIng Table for Parent Child relationship (accessories)

Accessories
=============
PkAccessoryId  Fk_ProductId(Child)  ProductId(Parent)
---------------------------------------------------------
1                      2                1              (A has B As accessory)
2                      1                2              (B has A as accessory)  --(//this record is an invalid entry due to the missing constraint and need to prevent this.)

How to add a unique constraint for (Fk_ProductId, ProductId) so that combination of Fk_Productid and ProductId cannot be added.

Example: Invalid case as this will make a loop of accessory
         1,  2
         2   1

i have already added a constraint as below.

ALTER TABLE Tx_ProductAccessories
  ADD CONSTRAINT UNI_CONS_Fk_ProductId_ProductId UNIQUE(Fk_ProductId, ProductId);

Also i am planning to prevent any illegal entry where it will cause a loop. like below.

 A-A   (Read product A has product A as accessory )
 A-B-A
 A-B-C-A
 A-B-C-B
 ...

Any product(P1) can be accessory of any other product(P2) as long as the accessory product P1 is not a parent product, grand parent product or anywhere in its Parents line which will cause a circular loop.

Upvotes: 3

Views: 67

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can do this by using computed columns:

alter table Tx_ProductAccessories
    add least_productId as (case when Fk_ProductId < ProductId
                                 then Fk_ProductId else ProductId end);

alter table Tx_ProductAccessories
    add greatest_productId as (case when Fk_ProductId < ProductId
                                    then ProductId else Fk_ProductId end);

alter table Tx_ProductAccessories
    add constraint uni_fkProductId_ProductId unique(least_productId, greatest_ProductId);

Upvotes: 4

Related Questions