How to Ensure Only One Record Has isDefault = 1 in a SQL Server Table?

Constraint for Only One Record Marked as Default

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

Solution: Using a Unique Filtered Index

For SQL Server 2008 or later, a unique filtered index can be used:

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

where the table is defined as follows:

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

Inserting multiple rows with the same FormID and isDefault set to 1 will result in the following error:

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

The above is the detailed content of How to Ensure Only One Record Has isDefault = 1 in a SQL Server Table?. For more information, please follow other related articles on the PHP Chinese website!