问题描述
我有一张桌子，类似于
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 约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！