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