rbhatup rbhatup - 7 months ago 11
SQL Question

Add special constraint to avoid duplicate rows with this condition?

I have a table that looks like this:

ClientId FloorNum BedNum IsActive
11 2 212 1
12 2 214 0
12 2 214 1
13 2 215 0
13 2 215 0
13 2 215 0
13 2 215 0
13 2 215 1
12 2 215 1


As you can see, FloorNum/BedNum combination 2/215 has two rows where IsActive equals 1. This cannot happen.

On the other hand, one FloorNum/BedNum combination can have many rows where IsActive equals 0.

How can I add a constraint to a table so that a FloorNum/BedNum combination can only have one row where IsActive = 1?

Any help is appreciated.

Answer

You can create a filtered unique index with a WHERE clause.

CREATE UNIQUE NONCLUSTERED INDEX IX_[index name]_FloorNum_BedNum ON [myTable] (
    FloorNum ASC,
    BedNum ASC)
WHERE (IsActive = 1)

This will only take into account records where the IsActive column is set to 1.

Based on your description I think that ClientId is not necessary in this example but if I am wrong you can add it to the index as well.