问题描述
考虑一个包含以下列的链接表:
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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!