DROP TABLE IF EXISTS - Cannot delete or update a parent row: a foreign key constraint fails

Question

I am trying to create a database for a hospital, with tables such as Patient, Ward etc. However, below I get the following error when I execute my SQL script. How can I fix this error?

DROP TABLE IF EXISTS STAFF | Error Code: 1217. Cannot delete or update a parent row: a foreign key constraint fails

When I comment out DROP TABLE IF EXISTS Staff I get this error instead:

My SQL script is below.
Many thanks.

USE test;

DROP TABLE IF EXISTS Ward;
DROP TABLE IF EXISTS PatientWard;
DROP TABLE IF EXISTS Patient;
DROP TABLE IF EXISTS NextOfKin;
DROP TABLE IF EXISTS NextOfKinTelephone;
DROP TABLE IF EXISTS Medication;
DROP TABLE IF EXISTS Prescription;
DROP TABLE IF EXISTS Staff;
DROP TABLE IF EXISTS StaffPatient;
DROP TABLE IF EXISTS Hospital;
DROP TABLE IF EXISTS HospitalTelephone;

CREATE TABLE Ward(
wardID INT(5) NOT NULL AUTO_INCREMENT,
hospitalID INT(5) default 0,
name VARCHAR(100) NOT NULL,
specialism VARCHAR(50) NOT NULL,
PRIMARY KEY(wardID),
FOREIGN KEY(hospitalID) REFERENCES Hospital(hospitalID) ON DELETE CASCADE ON UPDATE CASCADE,
INDEX name(name)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE PatientWard(
wardID INT(5) NOT NULL,
nationalInsuranceNo VARCHAR(20) default 0,
dateOfAdmission date NOT NULL,
note VARCHAR(250),
PRIMARY KEY(wardID, nationalInsuranceNo, dateOfAdmission),
FOREIGN KEY(wardID) REFERENCES Ward(wardID) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY(nationalInsuranceNo) REFERENCES Patient(nationalInsuranceNo) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE Patient(
nationalInsuranceNo VARCHAR(20) NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
dateOfBirth date NOT NULL,
sex VARCHAR(20) NOT NULL,
PRIMARY KEY(nationalInsuranceNo),
INDEX name(name)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE NextOfKin(
nextOfKinID INT(5) NOT NULL AUTO_INCREMENT,
nationalInsuranceNo VARCHAR(20) default 0,
name VARCHAR(100) NOT NULL,
PRIMARY KEY(nextOfKinID),
FOREIGN KEY(nationalInsuranceNo) REFERENCES Patient(nationalInsuranceNo) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8; 

CREATE TABLE NextOfKinTelephone(
nextOfKinID INT(5) default 0,
telephoneNumber VARCHAR(14) NOT NULL,
PRIMARY KEY(nextOfKinID, telephoneNumber),
FOREIGN KEY(nextOfKinID) REFERENCES NextOfKin(nextOfKinID) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE Medication(
NHS_ID INT(5) NOT NULL AUTO_INCREMENT,
hospitalID INT(5) default 0,
name VARCHAR(100) NOT NULL,
contraindications VARCHAR(200) NOT NULL,
PRIMARY KEY(NHS_ID),
FOREIGN KEY(hospitalID) REFERENCES Hospital(hospitalID) ON DELETE CASCADE ON UPDATE CASCADE,
INDEX name(name),
INDEX contraindications(contraindications)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE Prescription(
prescriptionID INT(5) NOT NULL AUTO_INCREMENT,
staffID INT(5) default 0,
NHS_ID INT(5) default 0,
nationalInsuranceNo INT(5) default 0,
startDate date NOT NULL,
dose VARCHAR(30) NOT NULL,
frequencyOfAdministration VARCHAR(100) NOT NULL,
endDate date NOT NULL,
PRIMARY KEY(prescriptionID),
FOREIGN KEY(staffID) REFERENCES Staff(staffID) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY(NHS_ID) REFERENCES Medication(NHS_ID) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY(nationalInsuranceNo) REFERENCES Patient(nationalInsuranceNo) ON DELETE RESTRICT ON UPDATE CASCADE,
INDEX startDate(startDate)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE Staff(
staffID INT(5) NOT NULL AUTO_INCREMENT,
hospitalID INT(5),
forename VARCHAR(50) NOT NULL,
surname VARCHAR(50) NOT NULL,
salary DECIMAL(6,2) NOT NULL,
position VARCHAR(50) NOT NULL,
PRIMARY KEY(staffID),
FOREIGN KEY(hospitalID) REFERENCES Hospital(hospitalID) ON DELETE CASCADE ON UPDATE CASCADE,
INDEX salary(salary)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE StaffPatient(
staffID INT(5) default 0,
nationalInsuranceNo INT(5) default 0,
PRIMARY KEY(staffID, nationalInsuranceNo),
FOREIGN KEY(staffID) REFERENCES Staff(staffID) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(nationalInsuranceNo) REFERENCES Patient(nationalInsuranceNo) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE Hospital(
hospitalID INT(5) NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
postcode VARCHAR(20) NOT NULL,
emailAddress VARCHAR(50) NOT NULL,
PRIMARY KEY(hospitalID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE HospitalTelephone(
hospitalID INT(5) default 0,
telephoneNumber VARCHAR(14),
PRIMARY KEY(hospitalID, telephoneNumber),
FOREIGN KEY(hospitalID) REFERENCES Hospital(HospitalID) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

Drop the FK before deleting your tables. Also create the FK after the table, so you can give it an explicid name

IF EXISTS ( SELECT * FROM SYS.foreign_keys WHERE NAME = 'FK_Ward_Hospital' AND parent_object_id = OBJECT_ID('dbo.Ward'))
ALTER TABLE dbo.Ward DROP CONSTRAINT [FK_Ward_Hospital] 

DROP TABLE IF EXISTS Ward;

CREATE TABLE Ward
(
    wardID INT(5) NOT NULL AUTO_INCREMENT,    
    hospitalID INT(5) default 0,
    name VARCHAR(100) NOT NULL,
    specialism VARCHAR(50) NOT NULL,
    PRIMARY KEY(wardID),
    INDEX name(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE [dbo].[Ward]  WITH CHECK ADD  CONSTRAINT [FK_Ward_Hospital] FOREIGN KEY([hospitalID])
REFERENCES [dbo].[Hospital] ([hospitalID])
ALTER TABLE [dbo].[Ward] CHECK CONSTRAINT [FK_Ward_Hospital]

Answer 2

The error message is telling you that the table cannot be dropped because of a problem with a parent row. (More on what the "problem" is later.) A parent row is a row in another table which is referencing a row in the table you are trying to drop. For example, in the StaffPatient table you have FOREIGN KEY(staffID) REFERENCES Staff(staffID). So, there are "parent rows" in StaffPatient which reference rows in Staff.

Now, for the foreign key from StaffPatient to Staff you have been careful enough to specify ON DELETE CASCADE ON UPDATE CASCADE, which means that when you delete rows from Staff parent rows on StaffPatient will also be deleted. So far, so good.

However, in the Prescription table you have a similar foreign key with ON DELETE RESTRICT ON UPDATE CASCADE. The RESTRICT keyword means that deletions of parent rows will be prevented. So, what is most likely happening is that there exist prescription rows which reference Staff rows, and are thus preventing your Staff table from being dropped.

One solution would be to drop the Prescription table before trying to drop the Staff table. However, it is theoretically possible to get into circular cascade situations, where the deletion is virtually impossible without resorting to the solution suggested in the answer by Chris Rodriguez, that is, drop all foreign keys before dropping tables.

Answer 3

Just posting here because I found the answer to my question here: mysql drop table and cascade delete to all references to the table

I used this code and my tables are now being created. The issue was that I was trying to drop tables that were linked to other tables with foreign keys. So by disabling and then re-enabling these foreign keys checks my tables can be created.

SET FOREIGN_KEY_CHECKS = 0;
drop table if exists <your_1st_table>;
drop table if exists <your_2nd_table>;
SET FOREIGN_KEY_CHECKS = 1;

Edit: you should drop your tables in the reverse order you created them, this might solve your issue.