CODEWITHSUNDEEP
sql-serverconstraints
BVernon
BVernon

Reputation: 3747

Add conditional unique check constraint?

I have a table with these fields: ItemID, CategoryID, IsPrimaryCategory.

The combination of ItemID and CategoryID should always be unique and that's the easy part. But I also want a constraint to force just ItemID to be unique but only when IsPrimaryCategory is true.

How could I best accomplish this?

Upvotes: 0

Views: 26

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

Use a unique filtered index:

create unique index uc_MyTable_ItemID_For_PrimaryCategory
  on MyTable(ItemID)
where IsPrimaryCategory = 1

Upvotes: 1

Related Questions