user2899211 user2899211 - 4 months ago 9
SQL Question

Insert into tables with primary and foreign key at same time

Very new to SQL and have spent a day on this already.

Here are my two tables:

Centre(cid, name, location, nurse_supervisor)
Nurse(nid, name, centre_id, certificate)

I have a big problem. The (nurse_supervisor) in Centre is a foreign key to Nurse (nid).
The (centre_id) in Nurse is a foreign key to (Centre cid).

I can't figure out how to populate these tables. I have tried:
INSERT ALL, which produces "A foreign key value has no matching primary key value"
I have tried removing the foreign key constraints and adding them after populating the tables but when I do that it says I can't add a constraint to tables with preexisting data.
I tried removing NOT NULL - but realized that was silly as the constraints will be enforced anyways.

Everything I look through says populate the parent table first and then the child, but these tables are linked to each other.

I am using SQL developer.

Answer

This is a poor schema design, but one way to get around it would be to:

  1. Make both centre_id and nurse_supervisor columns NULL in the two table definitions
  2. Insert all rows into both tables, but with NULL for those two columns
  3. Update centre_id to the correct value for each row in the Nurse table
  4. Update nurse_supervisor to the correct value for each row in the Centre table