MySQL无法添加外键约束

Question

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

错误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;

Answer 1

要查找特定错误，请运行以下命令：

SHOW ENGINE INNODB STATUS;

并查看该LATEST FOREIGN KEY ERROR部分。

子列的数据类型必须与父列完全匹配。例如，由于medicalhistory.MedicalHistoryID是INT，Patient.MedicalHistory也需要是INT，而不是SMALLINT。

另外，您应该set foreign_key_checks=0在运行DDL之前运行查询，以便可以以任意顺序创建表，而不需要在相关子表之前创建所有父表。