MySQL无法添加外键约束

本文翻译自：MySQL Cannot Add Foreign Key Constraint

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 错误1215（HY000）：无法添加外键约束

This is the SQL I’m using to create the tables, the two offending tables are Patient and Appointment . 这是我用来创建表的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;

#1楼

参考：https://stackoom.com/question/13bm9/MySQL无法添加外键约束

#2楼

To find the specific error run this: 要查找特定错误，请运行以下命令：

SHOW ENGINE INNODB STATUS;

And look in the LATEST FOREIGN KEY ERROR section. 并查看“ LATEST FOREIGN KEY ERROR部分。

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 . 例如，由于medicalhistory.MedicalHistoryID是INT ， Patient.MedicalHistory也需要一个INT ，而不是一个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. 另外，您应该在运行DDL之前运行查询set foreign_key_checks=0 ，以便可以以任意顺序创建表，而不需要在相关子表之前创建所有父表。

#3楼

Try to use the same type of your primary keys - int(11) - on the foreign keys - smallint(5) - as well. 尝试在外键-smallint（5）上使用相同类型的主键-int（11） -。

Hope it helps! 希望能帮助到你！

#4楼

Check following rules : 检查以下规则：

• First checks whether names are given right for table names 首先检查表名是否正确命名
• Second right data type give to foreign key ? 次权数据类型赋予外键？

#5楼

I had a similar error in creating foreign key in a Many to Many table where the primary key consisted of 2 foreign keys and another normal column. 在“多对多”表中创建外键时，我遇到了类似的错误，该表的主键由2个外键和另一个普通列组成。 I fixed the issue by correcting the referenced table name ie company, as shown in the corrected code below: 我通过更正引用的表名即company来解决此问题，如下面的更正代码所示：

create table company_life_cycle__history -- (M-M)
(
company_life_cycle_id tinyint unsigned not null,
Foreign Key (company_life_cycle_id) references company_life_cycle(id) ON DELETE    CASCADE ON UPDATE CASCADE,
company_id MEDIUMINT unsigned not null,
Foreign Key (company_id) references company(id) ON DELETE CASCADE ON UPDATE CASCADE,
activity_on date NOT NULL,
PRIMARY KEY pk_company_life_cycle_history (company_life_cycle_id, company_id,activity_on),
created_on datetime DEFAULT NULL,
updated_on datetime DEFAULT NULL,
created_by varchar(50) DEFAULT NULL,
updated_by varchar(50) DEFAULT NULL
);

#6楼

To set a FOREIGN KEY in Table B you must set a KEY in the table A. 要在表B中设置外键，必须在表A中设置键。

In table A: INDEX id ( id ) 在表A中：INDEX id （ id ）

And then in the table B, 然后在表B中，

CONSTRAINT `FK_id` FOREIGN KEY (`id`) REFERENCES `table-A` (`id`)