Constraint column value based on another column? SQL Server 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
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.