Add special constraint to avoid duplicate rows with this condition?

Question

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 1

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.