无论顺序如何，在两列上加上唯一约束

Question

我有以下表格定义：

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

我希望两个颜色列是唯一的，不管它们出现在哪个组合中。

例如，尝试：

INSERT INTO Car (FirstColorID, SecondColorID) VALUES (1, 2); --should succeed

但是在第一条记录存在且颜色相反的情况下尝试相同操作应该会失败:

INSERT INTO Car (FirstColorID, SecondColorID) VALUES (2, 1); --should violate constraint/check

我已经用高级代码解决了这个问题，但我更希望将约束直接强制执行在数据库中，最好不涉及触发器等内容。

谢谢。

- Ryan Weir

Answer 1

通过添加一个约束条件来避免这个问题，要求SecondColorID >= FirstColorID。这样可以限制数据库中的内容，超出您的期望范围。但是，如果您想查询特定的颜色模式，这个额外的要求将会很有用，因为您只需要查找SecondColorID = 1 AND FirstColorID = 2，而不必编写额外的逻辑来处理两个颜色ID的不可预测排序。

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

ALTER TABLE [Car] WITH CHECK
    ADD CHECK (SecondColorID >= FirstColorID);

- Sean U

---

这是一个创造性的方法。但现在我需要我的高级代码为插入/更新选择排序外键。虽然比我之前的方法更好，但仍然存在相同的基本问题。 - Ryan Weir

更好的选择可能是在您的存储过程中处理任何参数篡改。 - Sean U

Answer 2

创建计算列，使ComputedFirstColorID为FirstColorID和SecondColorID中较小的一个，而ComputedSecondColorID为FirstColorID和SecondColorID中较大的一个。现在进行索引操作。

CREATE UNIQUE NONCLUSTERED INDEX index_name ON Car (ComputedFirstColorID, ComputedSecondColorID)

应该没问题。

- Meff

Answer 3

你可以创建计算列，例如：

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

更新（在进行测试之前，您应该测试它，我只是进行了非常快速的测试）

想法：

整数占用 4 字节。如果我将 2 个整数放在一起，就会得到 8 字节。如果我对它们进行排序，就会得到唯一的 BigInt 值（8 字节）。

所以我做的是：

1. 确保它们按正确顺序排列
2. 将 32 位字节向左移位（因此仅通过乘以 4294967296 就可以得到我想要的结果）
3. 进行逻辑 OR- 这样我就得到了一个 8 字节的 BigInt 值，应该始终是唯一的！

所以：

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

- Jānis

---

我喜欢这种方法，但我不确定在性能方面使用计算字符串列是否可行。也许可以使用某种数值操作代替？我猜不能只是简单地将数字相乘，否则会出现各种冲突。 - Ryan Weir

谢谢，这就是我要用的。我喜欢这种方法，因为它完全包含在表定义中，而且应该足够高效。 - Ryan Weir

Answer 4

CREATE UNIQUE NONCLUSTERED INDEX index_name ON Car (FirstColorID, SecondColorID)

http://msdn.microsoft.com/en-us/library/ms188783.aspx

- Nicole Castle

---

这将防止两个 (1,2) 的条目 - 但是当 (1,2) 已经在表中时，这个约束无法阻止 (2,1) 的输入..... - marc_s