How to Ensure Only One Default Record Exists per Unique Identifier in SQL Server?

Maintaining a Single Default Record: A Constraint Enforcement

In database management systems, ensuring data integrity and consistency is crucial. One such scenario requires limiting the number of records marked as "default" to only one per set of rows identified by a unique identifier. How can this be achieved?

Unique Filtered Index Solution

For SQL Server versions 2008 and above, a unique filtered index offers an elegant solution. By creating a unique filtered index, as shown below:

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

...we ensure that only one record with its "isDefault" bit flag set to 1 can be present for any given FormID.

The underlying table structure is represented as:

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

Enforcing the Constraint

This constraint enforces that any attempt to insert multiple records with the same FormID and "isDefault" set to 1 will trigger an error, citing the presence of duplicate key values in the unique index. For instance, if multiple rows are inserted with matching FormIDs and "isDefault" flags set to 1, an error message will be generated:

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

By employing this technique, database administrators can effectively maintain the integrity of their data and ensure that only a single record is designated as the default record for any given set of rows.

The above is the detailed content of How to Ensure Only One Default Record Exists per Unique Identifier in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!