How Can I Enforce Conditional Uniqueness in SQL Server?

Implementing Conditional Unique Constraints in SQL Server

SQL Server's unique constraints prevent duplicate entries across specified columns. But how do you enforce uniqueness only under specific conditions? The solution lies in utilizing filtered indexes.

According to the SQL Server documentation, a filtered index is a nonclustered index that indexes only a subset of table rows, defined by a filter predicate.

This feature allows you to combine a unique index with a conditional filter to enforce uniqueness selectively. Consider a table:

Table(ID, Name, RecordStatus)

Where RecordStatus can be 1 (active) or 2 (deleted). To ensure unique ID values only when RecordStatus is 1, you'd create a filtered index:

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

This enforces uniqueness on ID only for active records (where RecordStatus = 1). Attempting to insert a duplicate ID with RecordStatus = 1 will result in an error:

<code>Cannot insert duplicate key row in object 'MyTable' with unique index 'MyIndex'. The duplicate key value is (9999).</code>

It's important to remember that filtered indexes were introduced in SQL Server 2008. For earlier versions (like SQL Server 2005), alternative approaches such as triggers are required to achieve conditional uniqueness.

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