CODEWITHSUNDEEP
sql-serverdatabaseconstraintsunique-constraint
Amol Hegana
Amol Hegana

Reputation: 157

SQL Server : achieve unique constraint except one value which can be repeated

I have a Student table having uniqueness on combined key [Name, Status]. I can mark particular student as deleted but later I can enroll him in. Basically I can have multiple entries with same name and Deleted status. But if I apply unique constraint, then it won't allow me to do so.

So I created a new function which accepts name and status. If status is deleted, then it return trues, if other than deleted, then it returns number of records.

Inside check I am checking if returned value is less than 1, only then I allow a new insert. When I invoke this function, it shows proper result but putting in CHECK constraint doesn't work.

Records

ID   Name    Status
-------------------
1    Ram     Deleted
1    Ram     Deleted
2    Sham    Active
2    Sham    Deleted

I can insert record for 

2 Sham Deleted

but it should not allow 

2 Sham Active

Thanks in advance

Upvotes: 2

Views: 1889

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You are looking for a filtered index. You want the student to be unique when not deleted.

    create unique index unq_student_id_active
        on student(name)
        where status = 'Active';

Filtered indexes in SQL Server are a recent addition; if they don't work on your version, tag your question with the version you are using.

Documentation

Upvotes: 12

Related Questions