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

Ensuring Single Default Record in Database Table

Problem

How can a constraint be implemented to restrict a database table such that only one record can possess a specific "isDefault" field value of 1? The constraint applies to a subset of records identified by a "FormID" field.

Solution: Unique Filtered Index for SQL Server 2008 or Higher

SQL Server 2008 and above provides a solution using unique filtered indexes:

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

Consider the following table structure:

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

Inserting multiple records with the same "FormID" and "isDefault" as 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).

This error effectively enforces the constraint, ensuring only one record per "FormID" can have "isDefault" set to 1.

(Source: https://technet.microsoft.com/en-us/library/cc280372.aspx)

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