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?

Rich McCluskey
  • 133
  • 4
  • Can you please show us the code you tried already and found to not work? – dfundako Aug 15 '16 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 '16 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 '16 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 '16 at 18:35
  • @sp_BlitzErik the cross-reference table would contain only the customer ID and address ID of the primary address relationship. Changing the primary address would only require updating the address ID. No need for any flags. – Shane Estelle Aug 15 '16 at 18:52
  • 2
    Related question: How to have a one-to-many relationship with a privileged child? – ypercubeᵀᴹ Aug 15 '16 at 21:31

1 Answers1

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

Mr.Brownstone
  • 13,102
  • 4
  • 36
  • 54
  • 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 '16 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 '16 at 18:44
  • Right, I guess then a filtered index is definitely the way to go. – Rich McCluskey Aug 15 '16 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 '16 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 '16 at 18:52
  • @RichMcCluskey true (about the chicken and egg problem). Sadly, SQL Server has not implemented deferrable constraints - which can alleviate the problem. – ypercubeᵀᴹ Aug 15 '16 at 20:03
  • I think you should NOT put [AddressId] in the unique index definition, as [AddressID] is the PK, so every combination of [AddressID], [CustomerID] and [IsPrimary] will be unique any way. – jyao Aug 15 '16 at 23:09
  • @jyao they have already fixed that. – ypercubeᵀᴹ Aug 16 '16 at 16:49