Geek Answers Handbook

UNIQUE bit column constraint

I have a table, something like

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

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

Can this limitation be defined in SQLServer 2008? (without using triggers)

PS The Setting parameter (FieldID, FormID, isDeleted) is unique, it adds the ability to mark one line as deleted, but I would like to be able to set n lines (for each FieldID, FormID) to isDeleted = 1 and only have one with isDeleted = 0

+5
source share
3 answers

, SQL Server 2008 UNIQUE ( man filter index, ), UNIQUE, .

:

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

IsDeleted DeletedDate DATETIME , . , DeletedDate, IsDeleted, , . , , DeletedDate ( FieldID FormId) IsDeleted. NULL.

, , . , , , .:)

+2

No, unique products are truly unique. You will have to either move your deleted records to another table or change IsDeleted to something that may be unique to all deleted records (for example, a timestamp). Any solution will require additional work, either in your application, or in a stored procedure, or in a trigger.

+1
source

More articles:


All Articles