SQL Server 2014 – Check Constraint for Exactly One Bit Column Set as True in Group

sql serversql server 2014

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?

Best Answer

You can define a filtered index on the table which will do this for you, there is no need to use a check constraint or an additional table for this. 

CREATE UNIQUE INDEX IX_Address_UniquePrimaryAddress 
ON dbo.Address(CustomerId) 
WHERE IsPrimary = 1;

Here is a good link of filtered indexes that should help you:

https://msdn.microsoft.com/en-us/library/cc280372.aspx

Related Question