本文介绍了T-SQL 唯一约束 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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-30 05:39