sql - UNIQUE constraint controlled by a bit column

Question

I have a table, something like

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

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

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

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

Answer 1

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.

An example of the filtered index:

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