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

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

The business_co开发者_开发百科ntact 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)

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

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 );

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 */;