您也许可以创建计算列,例如:
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)
)
更新(你应该之前测试一下,我只是做了非常快速的测试)
Idea:
整数为 4 个字节。如果我将 2 个整数放在一起,我会得到 8 个字节。如果我订购它们,我会得到唯一的 BigInt 值(8 个字节)。
所以我做什么:
- 我确保它们的顺序正确
- 我将 32 位的字节向左移动(所以只需乘以 4294967296 我就得到了我想要的)
- 我进行逻辑或 - 所以我得到 8 字节 BigInt 值,它应该始终是唯一的!
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)
)