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

Question

Hi,

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 only want to create a foreign key referencing the primary key of your business table

a good idea is probably to have your primary contact directly in your business table and make your business_contacts table just hold "secondary contacts"

pseudo code:

table business ( id PRIMARY KEY, col1, col2, col3, zip, street, country );
table business_contacts ( contact_id PRIMARY KEY, business_id FOREIGN KEY REFERENCES business.id,
   zip, street, country );

Answer 2

Can you make 'primary contact' nullable? Then you can insert a business, then a business_contact and then you can set the 'primary contact' field.

Alternatively for MySQL you can do (But I would not recommend it.):

/*!40014 SET FOREIGN_KEY_CHECKS=0 */;

... your statements ...

/*!40014 SET FOREIGN_KEY_CHECKS=1 */;

Answer 3

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