Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.

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.

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
– a_horse_with_no_nameApr 9 '15 at 7:53

Assuming version of SQL Server >= 2008, which is probably a fair assumption these days.
– Colin 't HartApr 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).
– Colin 't HartApr 9 '15 at 8:48

1 Answer
1

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