Let's say you have a table of addresses like the following:
dbo.Address ( AddressId INT PRIMARY KEY, CustomerId INT, IsPrimary BIT, ... more typical address fields ... )
I want a check constraint to enforce that there is always exactly one primary address per customer that allows me to switch primary addresses for a customer or add a new address and make that one primary.
I know I could have a constraint that not more than one is primary (allowing none to be primary) and then set all of a customer's address to not primary before inserting/updating an address to be primary. But how would I enforce that if a customer has addresses, one of them must always have IsPrimary = 1?