3

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?

Improve this question
6
  • Can you please show us the code you tried already and found to not work?
    – dfundako
    Aug 15, 2016 at 17:51
  • 1
    I think I'd store this information in a separate primary address cross-reference table, with the customer ID and the Address ID. Make the customer ID the primary key, so there could only be one row per customer, and have foreign keys to your customer and address tables.
    – Shane Estelle
    Aug 15, 2016 at 18:02
  • Just thinking that through, how would you change primary addresses? It would violate the constraint if you switch the primary to 0 in order to promote another address to primary, and you'd violate the constraint doing that in reverse order as well. Like Shane said, you should store this in another table.
    – Erik Darling
    Aug 15, 2016 at 18:06
  • There is no need for a check constraint or a new table to handle this as you can do it with a filtered index.
    – Mr.Brownstone
    Aug 15, 2016 at 18:35
  • 2
    Related question: How to have a one-to-many relationship with a privileged child?
    – ypercubeᵀᴹ
    Aug 15, 2016 at 21:31

1 Answer 1

Reset to default
5

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

Improve this answer
8
  • 2
    This will work for enforcing no more than one primary, but allows a customer to have addresses without one being set as primary.
    – Rich McCluskey
    Aug 15, 2016 at 18:40
  • That is very true, but as do the other solutions. You have to rely on some level of business logic to enforce that - for instance, using an additional table means you need logic in your insert to add the record to the new table. This would be the same using the index, you have to set the current primary to zero and the new primary to one.
    – Mr.Brownstone
    Aug 15, 2016 at 18:44
  • Right, I guess then a filtered index is definitely the way to go.
    – Rich McCluskey
    Aug 15, 2016 at 18:48
  • @RichMcCluskey your idea of adding a primary address ID column to the customer table would enforce a primary address.
    – Shane Estelle
    Aug 15, 2016 at 18:51
  • 2
    @ShaneEstelle yeah I thought more about that and I don't like that solution anymore because it creates a chicken and the egg problem. In order to add a customer you have to first have an address to set as primary. But to add an address you need a customer id
    – Rich McCluskey
    Aug 15, 2016 at 18:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.