title: MySQL Cannot Add Foreign Key Constraint
body: <p>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:</p>
  
  <blockquote>
    <p>ERROR 1215 (HY000): Cannot add foreign key constraint</p>
  </blockquote>
  
  <p>This is the SQL I'm using to create the tables, the two offending tables are <code>Patient</code> and <code>Appointment</code>.</p>
  
  <pre><code>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;
  </code></pre>