How to Ensure Only One Record Has an 'isDefault' Flag Set to 1 in SQL Server?

Ensuring Only One Record with 'isDefault' Flag

In a database table, you may wish to restrict data such that only one record per defined group can have a specific "isDefault" flag set to 1. This poses a challenge as the constraint is not table-wide but instead applies to a subset of rows identified by a unique ID.

Solution: Utilizing Unique Filtered Index

Modern SQL Server versions (2008 and later) offer a solution using unique filtered indexes. By creating such an index, you can enforce the constraint on the "isDefault" field for a specified set of rows.

Index Creation Syntax

For a table defined as follows:

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

You can create the unique filtered index using the following syntax:

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

This index will prevent duplicate key violations when attempting to insert multiple records with the same FormID and "isDefault" set to 1. For example:

INSERT INTO TableName(FormID, isDefault) VALUES (1, 1)
INSERT INTO TableName(FormID, isDefault) VALUES (1, 1) -- Will result in a unique key violation error

By establishing this index, you effectively ensure that only one record within each FormID set can possess the "isDefault" flag set to 1, maintaining the desired data integrity.

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