Foreign key on table A --> B, AND foreign key on table B --> A. How is this done?

Question

I have two tables - 'business' and 'business_contacts'.

The business_contact table has a many-to-one relationship with the business table. Furthermore, each business has a 'primary contact' field - which I'd assume is a one-to-many relationship with the business_contacts table.

The problem, of course, is that this creates a catch-22 for data insertion. Since neither field can be null, I can't insert a business_contact until I have a corresponding business, but I can't insert a business until I have a corresponding business_contact.

If anyone could help me get my head around how mutual one-to-many relationships are supposed to be dealt with I'd be most appreciative.

(Project being done in MySQL if it makes any difference)

Answer 1

You could remove the "primary contact" from your business table and move it to the business_contact table instead:

 business
  - business_id
  - name
  - address
  - blah

 business_contact
  - business_contact_id
  - business_id
  - is_primary_contact (bit field)

Determining the primary contact for a business thus becomes:

SELECT * FROM business_contact
WHERE business_id = <somevalue> AND is_primary_contact = 1