How to Enforce a Unique Default Flag in Database Records?

Enforcing Unique Default Flag in Database Records

In database management, it is often necessary to ensure that only one record within a specific dataset can be marked as default. For example, a system may have a collection of customer records where only one customer is designated as the default for billing purposes. To address this requirement, database constraints can be implemented.

Utilizing Unique Filtered Indexes

On SQL Server 2008 or later, the use of unique filtered indexes provides an efficient solution:

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

In this scenario, the table structure includes:

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

This index ensures that for any given FormID, only one record can have the isDefault flag set to 1. If multiple records with the same FormID attempt to have this flag set, an error will occur, such as:

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

By leveraging unique filtered indexes, you can effectively implement constraints to ensure that only one record per specified criteria (in this case, FormID) can be designated as default.

The above is the detailed content of How to Enforce a Unique Default Flag in Database Records?. For more information, please follow other related articles on the PHP Chinese website!