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 ?

Improve this question
6
  • Also similar to this question: Custom unique column constraint, only enforced if one column has a specific value
    – ypercubeᵀᴹ
    Commented May 30, 2013 at 16:38
  • 3
    What RDBMS are you using? In SQL Server 2008+ A unique filtered index can do this.
    – Martin Smith
    Commented May 30, 2013 at 16:39
  • SQL-Server and Postgres have partial indexes. In Oracle, similar functionality can be achieved as well.
    – ypercubeᵀᴹ
    Commented May 30, 2013 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
    Commented May 30, 2013 at 19:43
  • 2
    @MartinSmith You seem to have an answer here that is masquerading as a comment ;c)
    – Paul White ♦
    Commented May 30, 2013 at 23:01

1 Answer 1

Reset to default
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'
Improve this answer

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