T-SQL唯一约束，其中AnotherColumn = ParticularValue

Question

考虑一个包含以下列的链接表：

PersonID int NOT NULL
LocationID int NOT NULL
Active bit NOT NULL
...

系统允许每个人和位置独立配置。配置后，每个人最多只能链接到一个位置。如果人员移动到新位置，则该链接将被停用，而不是删除，以便系统知道该人员上次链接到特定位置的时间。一个人可以有任意数量的非活动链接，但最多只能有一个活动链接。一个位置可以有任意数量的人主动链接到该位置。

当第二个活动链接已经存在时，我如何向该表添加约束以防止为其创建第二个活动链接？

编辑:我以为这是一个2008年的盒子...现在是2005年了，所以过滤后的索引不起作用。

Answer 1

使用索引视图在早于2008的SQL Server版本上实现“筛选的索引”：

CREATE VIEW vOnlyOneActive
AS
  SELECT PersonID
  FROM <underlying table>
  WHERE Active = 1
GO
CREATE UNIQUE CLUSTERED INDEX IX_vOnlyOneActive on vOnlyOneActive (PersonID)
GO

为此，您需要打开正确的ANSI settings。

Answer 2

按照SilverSkin的建议，在Person表上有一个指向Location ID的链接，而不是一个链接表。现在，如果您想要一个非活动列表，可以向Person表添加一个触发器，以便在每次位置更改(<>最后一个历史表条目/person不存在)时插入到历史表中(对链接表的修改)。Person表中的链接给出了活动链接，而历史表给出了历史和(应该是人员在位置之间乒乓)位置历史的指示，而不是不活动的列表。

Answer 3

约束不能跨多行工作，因此您将需要一个INSERT/UPDATE trigger来为具有相同PersonID和LocationID的一组记录仅强制一条活动记录。