由位列控制的 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屋！