UNIQUE约束由位列控制 [英] UNIQUE constraint controlled by a bit column

问题描述

我有一张表，像

FieldsOnForms(
 FieldID int (FK_Fields)
 FormID int (FK_Forms)
 isDeleted bit
)

该对（FieldID，FormID ）应该是唯一的，但只有当行不被删除（isDeleted = 0）时。

The pair (FieldID,FormID) should be unique, BUT only if the row is not deleted (isDeleted=0).

可以在SQLServer 2008中定义这样的约束吗？ （不使用触发器）

Is it possible to define such a constraint in SQLServer 2008? (without using triggers)

设置（FieldID，FormID，isDeleted）是唯一的，可以将一行标记为已删除，但我想有机会将n行（每个FieldID，FormID）设置为isDeleted = 1，并且只有一个与isDeleted = 0

P.S. Setting (FieldID, FormID, isDeleted) to be unique adds the possibility to mark one row as deleted, but i would like to have the chance to set n rows (per FieldID,FormID) to isDeleted = 1, and to have only one with isDeleted = 0

推荐答案

您可以使用唯一的索引，使用SQL Server 2008 过滤后的索引功能，或者您可以对视图应用一个UNIQUE索引（穷人的过滤索引，适用于早期版本），但您不能拥有如您所描述的UNIQUE约束。

You can have a unique index, using the SQL Server 2008 filtered indexes feature, or you can apply a UNIQUE index against a view (poor man's filtered index, works against earlier versions), but you cannot have a UNIQUE constraint such as you've described.

过滤索引的示例：

 CREATE UNIQUE NONCLUSTERED INDEX IX_FieldsOnForms_NonDeletedUnique ON FieldsOnForms (FieldID,FormID) WHERE isDeleted=0

这篇关于UNIQUE约束由位列控制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！