Add conditional unique check constraint?

Question

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?

David Browne - Microsoft · Accepted Answer · 2019-11-07 20:41:33Z

1

Use a unique filtered index:

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

answered Nov 7, 2019 at 20:41

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BVernon Over a year ago

Awesome. Had no idea that was possible.

Collectives™ on Stack Overflow

Add conditional unique check constraint?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related