SQL server conditional unique constraint - Composite keys

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. Suppose there is a table named Person and columns 'Name' and 'isActive' Following should be the results

INSERT INTO Person (name,isActive) VALUES ('testUser1',0); --Success
INSERT INTO Person (name,isActive) VALUES ('testUser1',1); --Success
INSERT INTO Person (name,isActive) VALUES ('testUser1',0); --Success
INSERT INTO Person (name,isActive) VALUES ('testUser1',1); --Failure
INSERT INTO Person (name,isActive) VALUES ('testUser1',0); --Success

INSERT INTO Person (name,isActive) VALUES ('testUser2',0); --Success
INSERT INTO Person (name,isActive) VALUES ('testUser2',1); --Success
INSERT INTO Person (name,isActive) VALUES ('testUser2',0); --Success
INSERT INTO Person (name,isActive) VALUES ('testUser2',1); --Failure
INSERT INTO Person (name,isActive) VALUES ('testUser2',0); --Success

Basically what I need is that same name and active=1 rows should not be repeated.

Answer 1

Try a filtered unique index:

create unique index index_name on Person (name) where isActive = 1