Alternative approaches to "primary" record in child table 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?
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