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?