Create Unique Check + Additional Check in MS SQL Constraint

Question

I need to add a constraint to the following table:

Table(
CategoryId INT,
CustomerId INT,
IsActive BIT
....
)

I want to have only one combination of (CategoryId, CustomerId, IsActive = 1) just have no idea how to put that in one constraint. Only one combination of Category and Customer to be active (Active = 1) at the time, and be able to have multiple with active = 0

I know it can be done with function, but I'm trying to find a way without it. Something like:

CONSTRAINT UN_CK_Table UNIQUE(CategoryId, CustomerId, IsActive = 1)

Answer 1

You can use a filtered unique index:

CREATE UNIQUE INDEX YourTableUi1 ON YourTable (CategoryId, CustomerId) WHERE (IsActive = 1);