Home >Database >Mysql Tutorial >How Can I Enforce Conditional Uniqueness in SQL Server?

How Can I Enforce Conditional Uniqueness in SQL Server?

Patricia Arquette
Patricia ArquetteOriginal
2025-01-10 08:17:40504browse

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:

<code class="language-sql">Table(ID, Name, RecordStatus)</code>

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:

<code class="language-sql">CREATE UNIQUE INDEX MyIndex
ON MyTable(ID)
WHERE RecordStatus = 1;</code>

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!

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