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 ?

6
  • 3
    What RDBMS are you using? In SQL Server 2008+ A unique filtered index can do this. May 30 '13 at 16:39
  • SQL-Server and Postgres have partial indexes. In Oracle, similar functionality can be achieved as well. 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'

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .