MySQL 无法添加外键约束

MySQL Cannot Add Foreign Key Constraint

提问by joshuaegclark

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

ERROR 1215 (HY000): 无法添加外键约束

This is the SQL I'm using to create the tables, the two offending tables are Patientand 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;

回答by Ike Walker

To find the specific error run this:

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

SHOW ENGINE INNODB STATUS;

And look in the LATEST FOREIGN KEY ERRORsection.

并查看该LATEST FOREIGN KEY ERROR部分。

The data type for the child column must match the parent column exactly. For example, since medicalhistory.MedicalHistoryIDis an INT, Patient.MedicalHistoryalso 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=0before 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.

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

回答by Satsara Gunaratne

I had set one field as "Unsigned" and other one not. Once I set both columns to Unsigned it worked.

我已将一个字段设置为“未签名”，而另一字段未设置。一旦我将两列都设置为 Unsigned，它就起作用了。

回答by am0wa

• Engineshould be the same e.g. InnoDB
• Datatypeshould be the same, and with same length. e.g. VARCHAR(20)
• CollationColumns charset should be the same. e.g. utf8
  Watchout: Even if your tables have same Collation, columns still could have different one.
• Unique- Foreign key should refer to field that is unique (usually primary key)in the reference table.

• 引擎应该是相同的，例如 InnoDB
• 数据类型应该相同，并且长度相同。例如 VARCHAR(20)
• Collat​​ionColumns 字符集应该相同。例如 utf8
  Watchout：即使您的表具有相同的排序规则，列仍然可以具有不同的排序规则。
• 唯一- 外键应指代引用表中唯一的字段（通常是主键）。

回答by Felypp Oliveira

Try to use the same type of your primary keys - int(11)- on the foreign keys - smallint(5)- as well.

尝试在外键上使用相同类型的主键 - int(11)- smallint(5)- 以及。

Hope it helps!

希望能帮助到你！

回答by Goke Obasa

Confirm that the character encoding and collation for the two tables is the same.

确认两个表的字符编码和排序规则相同。

In my own case, one of the tables was using utf8and the other was using latin1.

在我自己的情况下，其中一个表正在使用utf8，另一个正在使用latin1.

I had another case where the encoding was the same but the collation different. One utf8_general_cithe other utf8_unicode_ci

我还有另一种情况，其中编码相同但排序规则不同。一个utf8_general_ci其他utf8_unicode_ci

You can run this command to set the encoding and collation for a table.

您可以运行此命令来设置表的编码和排序规则。

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

I hope this helps someone.

我希望这可以帮助别人。

回答by Singh

I had same problem and the solution was very simple. Solution : foreign keys declared in table should not set to be not null.

我有同样的问题，解决方案非常简单。解决方案：表中声明的外键不应设置为非空。

reference : If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL. (ref)

参考：如果您指定 SET NULL 操作，请确保您没有将子表中的列声明为 NOT NULL。（参考）

回答by user3707075

To set a FOREIGN KEY in Table B you must set a KEY in the table A.

要在表 B 中设置 FOREIGN KEY，您必须在表 A 中设置一个 KEY。

In table A: INDEX id(id)

在表 A 中：索引id( id)

And then in the table B,

然后在表 B 中，

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

回答by Bhaskar Bhatt

Check following rules :

检查以下规则：

• First checks whether names are given right for table names

• Second right data type give to foreign key ?

• 首先检查表名是否正确

• 第二个正确的数据类型给外键？

回答by Vijay Srinivas

Please ensure that both the tables are in InnoDB format. Even if one is in MyISAM format, then, foreign key constraint wont work.

请确保两个表都是 InnoDB 格式。即使是 MyISAM 格式，外键约束也不起作用。

Also, another thing is that, both the fields should be of the same type. If one is INT, then the other should also be INT. If one is VARCHAR, the other should also be VARCHAR, etc.

此外，另一件事是，两个字段都应该是相同的类型。如果一个是INT，那么另一个也应该是INT。如果一个是 VARCHAR，另一个也应该是 VARCHAR，等等。

回答by Learner

I faced the issue and was able to resolve it by making sure that the data types were exactly matching .

我遇到了这个问题，并且能够通过确保数据类型完全匹配来解决它。

I was using SequelPro for adding the constraint and it was making the primary key as unsigned by default .

我使用 SequelPro 来添加约束，它默认将主键设为无符号。