T-SQL 唯一约束 WHERE AnotherColumn = ParticularValue [英] T-SQL Unique Constraint WHERE AnotherColumn = ParticularValue

问题描述

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

Consider a link table with the following columns:

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

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

The system allows each Person and Location to be configured independently. Once configured, each Person can be linked to at most one Location. If a Person moves to a new Location, the link is to be deactivated, not deleted, so that the system knows when the Person was last linked to a particular Location. A Person can have any number of inactive links, but at most one active link. A Location can have any number of Persons actively linked to it.

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

How would I add a constraint to this table to prevent a second active link from being created for a Person when one already exists?

我以为这是 2008 年的盒子……结果是 2005 年，所以过滤索引不起作用.

I thought this was a 2008 box... turns out it's 2005, so filtered indexes won't work.

推荐答案

使用索引视图在 2008 之前的 SQL Server 版本上实现过滤索引":

Using an indexed view to implement a "filtered index" on versions of SQL Server predating 2008:

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

您需要拥有正确的 为此开启了 ANSI 设置.

这篇关于T-SQL 唯一约束 WHERE AnotherColumn = ParticularValue的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！