title: DROP TABLE IF EXISTS - Cannot delete or update a parent row: a foreign key constraint fails
body: <p><br><br>
  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?</p>
  
  <p><a href="https://i.stack.imgur.com/JYhFK.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JYhFK.png" alt="enter image description here"></a>
  DROP TABLE IF EXISTS STAFF | Error Code: 1217. Cannot delete or update a parent row: a foreign key constraint fails<br><br>
  When I comment out DROP TABLE IF EXISTS Staff I get this error instead: <a href="https://i.stack.imgur.com/O4Ujz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/O4Ujz.png" alt="enter image description here"></a></p>
  
  <p>My SQL script is below.<br>
  Many thanks.</p>
  
  <pre><code>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;
  </code></pre>