Condidate的检查约束

Question

我有任务持有为特定人的表。

TaskID INT PK 
PersonID INT (FK to Person Table) 
TaskStatusID INT (FK To list of Statuses) 
Deleted DATETIME NULL 

业务规则是一个人一次不能有多个活动任务。任务是基于TaskStatusID的“活动”。的状态有：

'5 =新，6 =在7 =进展，8 =在9 =评论，10 =完全，11 =取消'

这些人是我的状态表中的值。

所以，5,6,7,8和9的活动任务。这些休息是最后确定的。

一个人只能有一个任务是处于活动状态。

所以，测试，如果我能为这个人添加一个任务，我会做：

CASE EXISTS(SELECT * FROM Task WHERE PersonID = 123 AND TaskStatusIN IN (5,6,7,8,9)) THEN 0 ELSE 1 END AS CanAdd 

表有很多行。大约20万。

我想加入此表上的检查约束，所以在更新/插入的，我作出这样的查询，看看是否被添加的行/编辑将打破与问候的业务规则数据的完整性。

是检查约束适合这个，或者是有保留数据整体更有效的方式。

喜欢的东西：

ADD CONSTRAINT chk_task CHECK (
    EXISTS(SELECT * FROM Task WHERE PersonID = ?? AND TaskStatusIN IN (5,6,7,8,9))) 

Answer 1

您可以使用上面的检查约束，但最好的方法，我会建议好写DML触发器，插入前/更新之前，那一个抬高的声明。

Answer 2

你不能简单的用一个检查约束做到这一点，因为他们只（自然），可以对在同一行中列断言。有一些方法可以通过使用UDF查询其他行来解决这个问题，但是我见过的大多数实现都有奇怪的边界情况，可能会解决UDF问题，并最终导致无效行。

你可以做的是创造一个indexed view维持约束：

create table dbo.Tasks (
TaskID INT not null primary key, 
PersonID INT not null, 
TaskStatusID INT not null, 
Deleted DATETIME NULL 
) 
go 
create view dbo.DRI_Tasks_OneActivePerPerson 
with schemabinding 
as 
    select PersonID from dbo.Tasks 
    where TaskStatusID IN (5,6,7,8,9) 
go 
create unique clustered index UX_DRI_Tasks_OneActivePerPerson 
on dbo.DRI_Tasks_OneActivePerPerson (PersonID) 

现在这个插入成功（因为只有一行与工作状态的人1：

insert into dbo.Tasks (TaskID,PersonID,TaskStatusID) 
values (1,1,5),(2,1,1),(3,1,4) 

但该插入失败：

insert into dbo.Tasks (TaskID,PersonID,TaskStatusID) 
values (4,2,6),(5,2,8) 

随着消息：

Cannot insert duplicate key row in object 'dbo.DRI_Tasks_OneActivePerPerson' 
with unique index 'UX_DRI_Tasks_OneActivePerPerson'. 
The duplicate key value is (2). 

Answer 3

如果您使用的是SQL Server 2008或更高版本，您可以创建一个独特的过滤指数：

CREATE UNIQUE INDEX UQ_ActiveStatus 
ON dbo.Task (PersonID) 
WHERE TaskStatusID IN (5, 6, 7, 8, 9); 

这将作为唯一约束专为指定状态行。您只能拥有每个人的指定状态之一。