How can I achieve a unique constraint with two fields?

Question

I have a table with e.g. Name and IsDeleted fields. I want to add a row constraint so that only one Name value can have IsDeleted as 'false'. There can be many duplicate Name values, but they must all have IsDeleted asd true.

How would I write this check constraint ?

Also similar to this question: [Custom unique column constraint, only enforced if one column has a specific value](http://dba.stackexchange.com/questions/37427/custom-unique-column-constraint-only-enforced-if-one-column-has-a-specific-valu) — ypercubeᵀᴹ, May 30 '13 at 16:38
What RDBMS are you using? In SQL Server 2008+ A unique filtered index can do this. — Martin Smith, May 30 '13 at 16:39
SQL-Server and Postgres have partial indexes. In Oracle, similar functionality can be achieved as well. — ypercubeᵀᴹ, May 30 '13 at 17:37
@MartinSmith, I'm using MSSQL 2012 Express, so it should also be capable. I've just never heard of a filtered index until now, thanks. — ProfK, May 30 '13 at 19:43
@MartinSmith You seem to have an answer here that is masquerading as a comment ;c) — Paul White, May 30 '13 at 23:01
Yeah @MartinSmith,please migrate your comment to the answer box. — ProfK, May 31 '13 at 03:20

score 3 · Answer 1 · answered May 31 '13 at 07:24

Assuming the following table definition

 CREATE TABLE T
 (
 Name VARCHAR(50) NOT NULL,
 IsDeleted BIT NOT NULL
 )

Then you can achieve this with a unique index filtered to only include those Names that you wish to apply the constraint on.

CREATE UNIQUE INDEX ix ON T(Name) WHERE IsDeleted = 'false'

How can I achieve a unique constraint with two fields?

1 Answers1