sql - 避免 SQL 表中值(两列)的重复 "pairs"

Question

我有一个记录家庭成员之间关系的表格，我想确保始终只有一个记录中包含相同的两个人——但现在有两列和两个值需要考虑。我已经搜索了几个小时，虽然我确定它在那里，但我还没有找到它。

我有一张表，供其外键 (Persons.ID) 出现在 aID 和 bID(两个人)中的人使用。我有另一个角色表(母亲、女儿、叔叔、 sibling 等)，其外键 (Roles.ID) 将出现在 aID_role 和 bID_role 中。 (我试图在这个表中只存储整数，因为它最终可能会有很多记录。)

Relationships
----------------------------------------
aID     aID_role     bID     bID_role
----------------------------------------
 4        1           5        2

假设 aID 4 是 John，而 bID 5 是 Carla。 aID_role 1 是“父亲”，而 bID_role 2 是“女儿”。

如何防止输入下面的第二条记录？

Relationships
----------------------------------------
aID     aID_role     bID     bID_role
----------------------------------------
 4        1           5        2
 5        2           4        1 <-- sort of a duplicate

请注意，我对比较“_role”列不感兴趣。它们在这里无关紧要；我想避免 Person.IDs 4 和 5 出现在关系表中的多个记录中。

数据库位于 Azure SQL 中。

Answer 1

您可以创建镜像 id 的计算列，如下所示:

id1 AS (CASE WHEN aID < bID THEN aid ELSE bid END),
id2 AS (CASE WHEN aID < bID THEN bid ELSE aid END),

然后两个重复的行看起来像:

aID  bID  id1  id2
4    5    4    5
5    4    4    5

你只需要创建一个 UNIQUE 约束:

CONSTRAINT IX_Relationships UNIQUE (id1, id2)