Add a special restriction 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, the combination FloorNum / BedNum 2/215 has two lines, where IsActive is 1. This cannot be.

On the other hand, a single FloorNum / BedNum combination can have many rows, where IsActive is 0.

How to add a constraint to a table so that the FloorNum / BedNum combination has only 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 only considers records where the IsActive column IsActive set to 1 .

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

Answer 2

You can add Trigger to your table to check this condition.

 create TRIGGER tr_check_unique ON YourTable FOR INSERT AS declare @FloorNum int; declare @BedNum int; select @FloorNum=i.FloorNum from inserted i; select @BedNum=i.BedNum from inserted i; begin try if exists(select FloorNum ,BedNum from YourTable where FloorNum=@FloorNum and BedNum=@BedNum and IsActive=1) raiserror ('',16,1) end try begin catch raiserror ('record is not unique',16,1) end catch