MySQL无法添加外键约束

阳光穿透心脏的1/2处 2022-11-28 13:54 136阅读 0赞

本文翻译自：[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无法添加外键约束][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`)

[MySQL Cannot Add Foreign Key Constraint]: https://oldbug.net/q/13bm9/MySQL-Cannot-Add-Foreign-Key-Constraint
[https_stackoom.com_question_13bm9_MySQL]: https://stackoom.com/question/13bm9/MySQL%E6%97%A0%E6%B3%95%E6%B7%BB%E5%8A%A0%E5%A4%96%E9%94%AE%E7%BA%A6%E6%9D%9F