Sql – conditional unique constraint


I have a situation where i need to enforce a unique constraint on a set of columns, but only for one value of a column.

So for example I have a table like Table(ID, Name, RecordStatus).

RecordStatus can only have a value 1 or 2 (active or deleted), and I want to create a unique constraint on (ID, RecordStatus) only when RecordStatus = 1, since I don't care if there are multiple deleted records with the same ID.

Apart from writing triggers, can I do that?

I am using SQL Server 2005.

Best Solution

Behold, the filtered index. From the documentation (emphasis mine):

A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.

And here's an example combining a unique index with a filter predicate:

create unique index MyIndex
on MyTable(ID)
where RecordStatus = 1;

This essentially enforces uniqueness of ID when RecordStatus is 1.

Following the creation of that index, a uniqueness violation will raise an arror:

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

Note: the filtered index was introduced in SQL Server 2008. For earlier versions of SQL Server, please see this answer.