Constraint for only one record marked as default

Question

How could I set a constraint on a table so that only one of the records has its isDefault bit field set to 1?

The constraint is not table scope, but one default per set of rows, specified by a FormID.

Answer 1

Use a unique filtered index

On SQL Server 2008 or higher you can simply use a unique filtered index

CREATE UNIQUE INDEX IX_TableName_FormID_isDefault     ON TableName(FormID)     WHERE isDefault = 1 

Where the table is

CREATE TABLE TableName(     FormID INT NOT NULL,     isDefault BIT NOT NULL ) 

For example if you try to insert many rows with the same FormID and isDefault set to 1 you will have this error:

Cannot insert duplicate key row in object 'dbo.TableName' with unique index 'IX_TableName_FormID_isDefault'. The duplicate key value is (1).

Source: http://technet.microsoft.com/en-us/library/cc280372.aspx