Constraint column value based on another column? SQL Server

Question

i am trying to set my defaults column Y or N based on membershipType column. There can be only one Y in defaults column based on the 3 different value in membershipType column. Other than that, others will be N. How do i do it?

ID titlePromo membershipType defaults
-- ---------- -------------- ------
1 Promo 1 Membership Promotion Y
2 Promo 2 Membership Renewal Y
3 Promo 3 Membership Grad Y
4 Promo 4 Membership Promotion N
5 Promo 5 Membership Promotion N
6 Promo 6 Membership Grad N

Answer 1

Use a unique filtered index.

CREATE UNIQUE INDEX uixDefault 
ON dbo.Promotions(membershipType)
WHERE defaults = 'Y';

This stops there being a second row with defaults='Y' of the same membershipType.

• lel: an error occur : The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Promotions' and the index name 'uixDefault'. The duplicate key value is (Membership Promotion ). The statement has been terminated.
• lel: my table name is dbo.Promotions
• Rob Farley: Do you have multiple rows with "Membership Promotion", that have Y?
• lel: oh my fault, there are duplicates of y in same promotion type. It works thanks!
• Rob Farley: No worries. Happy to help. :)