How Can I Create a Unique Constraint on a Table Column Only When a Specific Condition is Met?

Implementing Conditional Uniqueness: A Superior Alternative to Triggers

Often, database design requires unique constraints on table columns, but only under specific circumstances. Let's illustrate this with an example:

Consider a table Table with columns ID, Name, and RecordStatus. RecordStatus can be 1 (active) or 2 (deleted). The goal is to enforce uniqueness on the ID column only for active records (where RecordStatus = 1), allowing duplicates for deleted records.

Why Avoid Triggers?

While triggers could achieve this, a more efficient and robust solution is using filtered indexes.

The Power of Filtered Indexes

Filtered indexes create indexes on a subset of table data, defined by a filter condition. This allows for precise control over indexing, ideal for conditional unique or primary key constraints.

Creating the Conditional Unique Constraint

To enforce uniqueness on ID only when RecordStatus is 1, use this command:

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

This creates a unique index on the ID column, but only considers rows where RecordStatus equals 1.

Expected Behavior

Attempting to insert a duplicate ID value with RecordStatus = 1 will result in an error, similar to this:

<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>

Summary

Filtered indexes offer a clean and efficient approach to creating conditional unique constraints. They enhance data integrity without the performance overhead often associated with triggers, providing a more scalable and maintainable solution for complex database requirements. By precisely defining the filter condition, you can selectively enforce uniqueness, aligning perfectly with specific business rules.

The above is the detailed content of How Can I Create a Unique Constraint on a Table Column Only When a Specific Condition is Met?. For more information, please follow other related articles on the PHP Chinese website!