Foreign key on table A & # 8594; B and foreign key on the table B & # 8594; A. How is this done?

Question

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

The business_contact table is a many-to-one relationship with the business table. In addition, each business has a primary contact field, which I believe is a one-to-many relationship to the business_contacts table.

The problem, of course, is that this creates catch-22 for inserting data. Since no field can be null, I cannot insert business_contact until I have a corresponding business, but I can not insert a business until I have a corresponding business_contact.

If someone could help me figure out how a one-to-many relationship should interact, I would be very grateful.

(The project is executed in MySQL, if it matters)

Answer 1

You can remove the "primary contact" from your business table and transfer it to the business_contact table:

 business
  - business_id
  - name
  - address
  - blah

 business_contact
  - business_contact_id
  - business_id
  - is_primary_contact (bit field)

The definition of a primary contact for a business thus becomes:

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

Answer 2

you want to create a foreign key that references the primary key of the table business

, , , - business_contacts " "

:

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 3

Can you make the "primary contact" null? Then you can insert the business, then business_contact, and then you can set the "primary contact" field.

As an alternative to MySQL, you can do (but I would not recommend it.):

/*!40014 SET FOREIGN_KEY_CHECKS=0 */;

... your statements ...

/*!40014 SET FOREIGN_KEY_CHECKS=1 */;