I have a database with a Customers table, and two child tables Addresses and Contacts, as customers often have multiple locations and contacts.

However we also need a "primary address" and a "primary contact" for each customer that we use as the default.

My current design was to put a PrimaryContactID and a PrimaryAddressID in the Customers table (both are nullable), but I was never very comfortable with this design - a parent table referencing the child table doesn't feel right.

The alternative approach I considered was an IsPrimary bit field on the child tables. This would need a trigger that would unset the IsPrimary value on the other records and maintain integrity but could lead to problems if a query tried to set multiple records as primary for the same customer: I'd have to write the trigger to fail in these situations.

Any ideas/comments or other approaches I should consider?

  • You can create a filtered unique index to make sure only a single isPrimary exists per customer: create unique index on addresses (customer_id) where isPrimary = 1 Apr 9 '15 at 7:53
  • Assuming version of SQL Server >= 2008, which is probably a fair assumption these days. Apr 9 '15 at 8:44
  • I have absolutely no problem with "circular" foreign keys. It should be impossible to delete an address or contact that is still being used as "primary address" or "primary contact". You can use cascading constraints to make it possible to delete all records in one go if absolutely necessary, but the foreign keys are almost certainly optional to support creation of customers before the addresses and contacts are added (although deferred constraints could also be used, but I'm pretty sure SQL Server doesn't have deferrable constraints). Apr 9 '15 at 8:48

The other design option that came in my mind is to use CustomerID column in child table like Addresses and Contacts so that you will be making entry first in Customer table and that CustomerID will be referred in other table.

As suggested in comment by @a_horse_with_no_name, you can create Unique Index as

CREATE UNIQUE INDEX UniquePrimaryAddresss ON Addresses(CustomerID) WHERE IsPrimary = 1

Similar indexes on Contacts table as well

  • Thanks for the idea - I'd not thought of having a filtered unique index in that way - helps enforce the constraint. I'll have to experiment a bit
    – Quango
    Apr 9 '15 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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