CODEWITHSUNDEEP
sqlsql-servercheck-constraints
Stefan Taseski
Stefan Taseski

Reputation: 232

Create Unique Check + Additional Check in MS SQL Constraint

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)

Upvotes: 1

Views: 90

Answers (1)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52117

You can use a filtered unique index:

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

Upvotes: 4

Related Questions