conditional unique constraint

Question

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.

Answer 1

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

Answer 2

Add a check constraint like this. The difference is, you'll return false if Status = 1 and Count > 0.

http://msdn.microsoft.com/en-us/library/ms188258.aspx

CREATE TABLE CheckConstraint (   Id TINYINT,   Name VARCHAR(50),   RecordStatus TINYINT ) GO  CREATE FUNCTION CheckActiveCount(   @Id INT ) RETURNS INT AS BEGIN    DECLARE @ret INT;   SELECT @ret = COUNT(*) FROM CheckConstraint WHERE Id = @Id AND RecordStatus = 1;   RETURN @ret;  END; GO  ALTER TABLE CheckConstraint   ADD CONSTRAINT CheckActiveCountConstraint CHECK (NOT (dbo.CheckActiveCount(Id) > 1 AND RecordStatus = 1));  INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2); INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2); INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2); INSERT INTO CheckConstraint VALUES (1, 'No Problems', 1);  INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 1); INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 2); -- Msg 547, Level 16, State 0, Line 14 -- The INSERT statement conflicted with the CHECK constraint "CheckActiveCountConstraint". The conflict occurred in database "TestSchema", table "dbo.CheckConstraint". INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 1);  SELECT * FROM CheckConstraint; -- Id   Name         RecordStatus -- ---- ------------ ------------ -- 1    No Problems  2 -- 1    No Problems  2 -- 1    No Problems  2 -- 1    No Problems  1 -- 2    Oh no!       1 -- 2    Oh no!       2  ALTER TABLE CheckConstraint   DROP CONSTRAINT CheckActiveCountConstraint;  DROP FUNCTION CheckActiveCount; DROP TABLE CheckConstraint;