Is it possible to have a complex Check Constraint?
for example I have a customers table each record of which is assigned a customer type.
therefore I could have several customers with the same type.
I want to set one customer for each type to be the "Primary" account for that type, however only one customer for each type can be Primary.
Is it possible to add a check constraint on the IsPrimary field that checks all customers of the same type to see if there is already one marked as primary?
I can do this in C# code obviously but I would like this as an extra check.
I could also use a trigger but I would like to stick to constraints so that I can handle errors in the same way for all errors.
Thanks