How Can I Enforce Conditional Unique Constraints in SQL Server Without Using Triggers?

Achieving Conditional Unique Constraints in SQL Server: A Trigger-Free Approach

Maintaining data integrity often necessitates unique constraints applied selectively to subsets of data. While triggers are a common solution, SQL Server provides a more elegant method using filtered indexes.

Leveraging Filtered Indexes for Conditional Uniqueness

A filtered index indexes only a portion of a table's rows, determined by a filter condition. This allows for conditional unique constraints.

Consider a scenario requiring uniqueness for (ID, RecordStatus) only when RecordStatus is 1. The solution is straightforward:

CREATE UNIQUE INDEX MyIndex
ON MyTable(ID)
WHERE RecordStatus = 1;

This index only indexes rows where RecordStatus = 1, thus enforcing uniqueness for this subset. Violating this constraint results in an error:

<code>Msg 2601, Level 14, State 1, Line 13
Cannot insert duplicate key row in object 'dbo.MyTable' with unique index 'MyIndex'. The duplicate key value is (9999).</code>

Important Consideration

Filtered indexes were introduced in SQL Server 2008. For earlier versions, triggers or a combination of unique constraints and row versioning techniques are necessary alternatives.

The above is the detailed content of How Can I Enforce Conditional Unique Constraints in SQL Server Without Using Triggers?. For more information, please follow other related articles on the PHP Chinese website!