Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question

marked as duplicate by ypercube, RolandoMySQLDBA, dezso, gbn, Jon Seigel May 31 '13 at 13:50

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

    
Also similar to this question: Custom unique column constraint, only enforced if one column has a specific value –  ypercube May 30 '13 at 16:38
3  
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
2  
@MartinSmith You seem to have an answer here that is masquerading as a comment ;c) –  Paul White May 30 '13 at 23:01

1 Answer 1

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'
share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.