1

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 ?

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.

  • 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
3

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'

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