How to insert data in child table(Tb1) of parent table(Tb2) that is also the parent(Tb2) of child(Tb1) in MySQL?

Question

I am very new to MySQL, just started on it a few days ago. For my homework, the MySQL has its default set to MyISAM for simplicity's sake, and had me created a hospital example database for practice on MySQL.

But for my own personal purpose and some research that InnoDB actually enforces the FK compared to MyISAM, I switched the default to InnoDB and tried to recreate the same database I did for my homework with MyISAM.

And then the problem whacked me like a truck, my "ward" table is the following:

create table Ward
(Code varchar(1) primary key not null,
Name varchar(15) not null,
Consultant varchar(3) not null);

alter table ward
add foreign key (Consultant)
references Doctor(ID);

and my doctor table goes like this:

create table Doctor
(ID varchar(3) primary key not null,
Name varchar(15) not null,
Ward varchar(1) not null);

alter table doctor
add foreign key (Ward)
references Ward(Code);

After that, I tried entering data in either of the tables, and the following errors were given to me when I try to enter data into "ward" table and "doctor" table respectively:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (hospital.ward, CONSTRAINT ward_ibfk_1 FOREIGN KEY (Consultant) REFERENCES doctor (ID))

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (hospital.doctor, CONSTRAINT doctor_ibfk_1 FOREIGN KEY (Ward) REFERENCES ward (Code))

How do I enter data into either of the tables?

Thanks and sorry for the trouble!

Answer 1

It is probably 'wrong' to have FKs going back and forth like that.

You can disable foreign keys while you create the tables. But then you may have trouble with inserts -- one insert will find a FK violation since you have not done the other yet.

If you don't have loops in your FKs, then simply create the tables in the 'right' order.