MySQL Cannot Add Foreign Key Constraint
foreign-keys, mysql, sql
Solution
To find the specific error run this:
SHOW ENGINE INNODB STATUS;
And look in the `LATEST FOREIGN KEY ERROR` section.
The data type for the child column must match the parent column exactly. For example, since `medicalhistory.MedicalHistoryID` is an `INT`, `Patient.MedicalHistory` also needs to be an `INT`, not a `SMALLINT`.
Also, you should run the query `set foreign_key_checks=0` before running the DDL so you can create the tables in an arbitrary order rather than needing to create all parent tables before the relevant child tables.
Problem
So I'm trying to add Foreign Key constraints to my database as a project requirement and it worked the first time or two on different tables, but I have two tables on which I get an error when trying to add the Foreign Key Constraints. The error message that I get is: ERROR 1215 (HY000): Cannot add foreign key constraint This is the SQL I'm using to create the tables, the two offending tables are `Patient` and `Appointment`. ``` SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=1; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; CREATE SCHEMA IF NOT EXISTS `doctorsoffice` DEFAULT CHARACTER SET utf8 ; USE `doctorsoffice` ; -- ----------------------------------------------------- -- Table `doctorsoffice`.`doctor` -- ----------------------------------------------------- DROP TABLE IF EXISTS `doctorsoffice`.`doctor` ; CREATE TABLE IF NOT EXISTS `doctorsoffice`.`doctor` ( `DoctorID` INT(11) NOT NULL AUTO_INCREMENT , `FName` VARCHAR(20) NULL DEFAULT NULL , `LName` VARCHAR(20) NULL DEFAULT NULL , `Gender` VARCHAR(1) NULL DEFAULT NULL , `Specialty` VARCHAR(40) NOT NULL DEFAULT 'General Practitioner' , UNIQUE INDEX `DoctorID` (`DoctorID` ASC) , PRIMARY KEY (`DoctorID`) ) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `doctorsoffice`.`medicalhistory` -- ----------------------------------------------------- DROP TABLE IF EXISTS `doctorsoffice`.`medicalhistory` ; CREATE TABLE IF NOT EXISTS `doctorsoffice`.`medicalhistory` ( `MedicalHistoryID` INT(11) NOT NULL AUTO_INCREMENT , `Allergies` TEXT NULL DEFAULT NULL , `Medications` TEXT NULL DEFAULT NULL , `ExistingConditions` TEXT NULL DEFAULT NULL , `Misc` TEXT NULL DEFAULT NULL , UNIQUE INDEX `MedicalHistoryID` (`MedicalHistoryID` ASC) , PRIMARY KEY (`MedicalHistoryID`) ) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Table `doctorsoffice`.`Patient` -- ----------------------------------------------------- DROP TABLE IF EXISTS `doctorsoffice`.`Patient` ; CREATE TABLE IF NOT EXISTS `doctorsoffice`.`Patient` ( `PatientID` INT unsigned NOT NULL AUTO_INCREMENT , `FName` VARCHAR(30) NULL , `LName` VARCHAR(45) NULL , `Gender` CHAR NULL , `DOB` DATE NULL , `SSN` DOUBLE NULL , `MedicalHistory` smallint(5) unsigned NOT NULL, `PrimaryPhysician` smallint(5) unsigned NOT NULL, PRIMARY KEY (`PatientID`) , UNIQUE INDEX `PatientID_UNIQUE` (`PatientID` ASC) , CONSTRAINT `FK_MedicalHistory` FOREIGN KEY (`MEdicalHistory` ) REFERENCES `doctorsoffice`.`medicalhistory` (`MedicalHistoryID` ) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_PrimaryPhysician` FOREIGN KEY (`PrimaryPhysician` ) REFERENCES `doctorsoffice`.`doctor` (`DoctorID` ) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `doctorsoffice`.`Appointment` -- ----------------------------------------------------- DROP TABLE IF EXISTS `doctorsoffice`.`Appointment` ; CREATE TABLE IF NOT EXISTS `doctorsoffice`.`Appointment` ( `AppointmentID` smallint(5) unsigned NOT NULL AUTO_INCREMENT , `Date` DATE NULL , `Time` TIME NULL , `Patient` smallint(5) unsigned NOT NULL, `Doctor` smallint(5) unsigned NOT NULL, PRIMARY KEY (`AppointmentID`) , UNIQUE INDEX `AppointmentID_UNIQUE` (`AppointmentID` ASC) , CONSTRAINT `FK_Patient` FOREIGN KEY (`Patient` ) REFERENCES `doctorsoffice`.`Patient` (`PatientID` ) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_Doctor` FOREIGN KEY (`Doctor` ) REFERENCES `doctorsoffice`.`doctor` (`DoctorID` ) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `doctorsoffice`.`InsuranceCompany` -- ----------------------------------------------------- DROP TABLE IF EXISTS `doctorsoffice`.`InsuranceCompany` ; CREATE TABLE IF NOT EXISTS `doctorsoffice`.`InsuranceCompany` ( `InsuranceID` smallint(5) NOT NULL AUTO_INCREMENT , `Name` VARCHAR(50) NULL , `Phone` DOUBLE NULL , PRIMARY KEY (`InsuranceID`) , UNIQUE INDEX `InsuranceID_UNIQUE` (`InsuranceID` ASC) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `doctorsoffice`.`PatientInsurance` -- ----------------------------------------------------- DROP TABLE IF EXISTS `doctorsoffice`.`PatientInsurance` ; CREATE TABLE IF NOT EXISTS `doctorsoffice`.`PatientInsurance` ( `PolicyHolder` smallint(5) NOT NULL , `InsuranceCompany` smallint(5) NOT NULL , `CoPay` INT NOT NULL DEFAULT 5 , `PolicyNumber` smallint(5) NOT NULL AUTO_INCREMENT , PRIMARY KEY (`PolicyNumber`) , UNIQUE INDEX `PolicyNumber_UNIQUE` (`PolicyNumber` ASC) , CONSTRAINT `FK_PolicyHolder` FOREIGN KEY (`PolicyHolder` ) REFERENCES `doctorsoffice`.`Patient` (`PatientID` ) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_InsuranceCompany` FOREIGN KEY (`InsuranceCompany` ) REFERENCES `doctorsoffice`.`InsuranceCompany` (`InsuranceID` ) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB; USE `doctorsoffice` ; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; ```