sql - 如何为 SQL 中的列组合提供唯一约束，而不考虑 Order

Question

我有两张 table 。第一个是保存数据的产品表，第二个保存父子关系。

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

如何为 (Fk_ProductId, ProductId) 添加唯一约束，以便无法添加 Fk_Productid 和 ProductId 的组合。

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

我已经添加了如下约束。

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

我还计划防止任何会导致循环的非法输入。如下所示。

 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.

Answer 1

您可以使用计算列来做到这一点:

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);