MySQL Cannot Add Foreign Key Constraint

Question

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;

Answer 1

Run this code to find the specific error:

SHOW ENGINE INNODB STATUS; 

Also, have a look at this FOREIGN KEY ERROR section:

• Data type for the child column should exactly match with parent column.

 Example: medicalhistory.MedicalHistoryID is an INT so, Patient.MedicalHistory also needs to be an INT, it should not be SMALLINT.

• Before running the DDL, you should run the query set foreign_key_checks=0, so that you can create tables in arbitrary order. So that, you don’t need to create all the parent tables before creating the relevant child tables.