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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

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

Assuming the following table definition


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.