Home >Database >Mysql Tutorial >How to Ensure Only One Record Has isDefault = 1 in a SQL Server Table?

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

Mary-Kate Olsen
Mary-Kate OlsenOriginal
2025-01-01 03:20:10227browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn