MySQL无法添加外键约束

因此，我作为项目需求试图将外键约束添加到数据库中，并且它第一次或在两个不同的表上运行，但是在尝试添加外键约束时，我在两个表上遇到错误。我收到的错误消息是：

错误1215（HY000）：无法添加外键约束

这是我用来创建表的SQL，两个有问题的表是Patient和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;