CODEWITHSUNDEEP
sqlsql-serverunique-constraintcomposite-key
VIVEK P S
VIVEK P S

Reputation: 315

SQL server conditional unique constraint - Composite keys

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.

Upvotes: 1

Views: 190

Answers (1)

Gareth Lyons
Gareth Lyons

Reputation: 1972

Try a filtered unique index:

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

Upvotes: 3

Related Questions