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;
546665 次浏览
小开
最佳答案

要找到特定的错误，运行以下命令:

SHOW ENGINE INNODB STATUS;

并查看LATEST FOREIGN KEY ERROR部分。

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

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

小开

试着在外键上使用相同类型的主键——int (11)——以及短整型(5)。

希望能有所帮助!

小开

检查以下规则:

  • 首先检查是否为表名指定了正确的名称

  • 第二个右数据类型给外键?

小开

我在多对多表中创建外键时遇到了类似的错误，其中主键由2个外键和另一个正常列组成。我通过纠正引用表名称即公司修正了这个问题，如下所示的修正代码:

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
);
小开

要在表B中设置一个外键，必须在表a中设置一个键。

表A: INDEX id (id)

然后在表B中，

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

我有类似的错误与两个外键不同的表，但具有相同的键名!我重命名键和错误已经消失)

小开

我将一个字段设置为“Unsigned”，另一个字段则没有。一旦我把两列都设置为Unsigned，它就工作了。

小开

有一个类似的错误，但在我的情况下，我错过了将pk声明为auto_increment。

万一对别人有用呢

小开
我也遇到过同样的问题，解决方法很简单。 解决方法:表中声明的外键不能设为非空

引用:如果您指定了SET NULL操作，请确保您没有将子表中的列声明为not NULL。(ref ) < / p >

小开

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

另外，这两个字段应该是相同类型的。如果一个是INT型，那么另一个也应该是INT型。如果一个是VARCHAR，另一个也应该是VARCHAR，以此类推。

小开

我有同样的问题，然后我在父表和子表中更正了引擎名称为Innodb，并更正了引用字段名 外键(c_id)引用x9o_parent_table(c_id)
然后它工作正常，表安装正确。

小开

我得到了同样的错误。我的原因是:

  1. 我通过phpmyadmin通过复制整个数据库创建了一个数据库备份。
  2. 我创建了一个新的db与旧db的名称相同，并选择了它。
  3. 我启动了一个SQL脚本来创建更新的表和数据。
  4. 我得到了错误。此外，当我禁用foreign_key_checks。尽管数据库是完全空的。

原因是:因为我使用phpmyadmin在重命名的数据库中创建了一些外键-这些外键是用数据库名称前缀创建的，但数据库名称前缀没有更新。所以在backup-db中仍然有指向新创建的db的引用。

小开
  • 引擎应该是相同的例如InnoDB
  • 数据类型应该相同，并且具有相同的长度。比如VARCHAR (20)
  • 排序列字符集必须相同。例如utf8 < br > 注意:即使你的表有相同的排序规则，列仍然可能有不同的排序规则
  • 独特的 -外键应该指向引用表中唯一的(通常是主键)字段。
小开

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

我使用SequelPro来添加约束，默认情况下，它将主键设置为unsigned。

小开

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

在我自己的例子中，一个表使用utf8，另一个表使用latin1。

我有另一种情况，编码是相同的，但排序不同。一个utf8_general_ci，另一个utf8_unicode_ci

可以使用此命令设置表的编码和排序规则。

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

我希望这能帮助到一些人。

小开

我的解决方案可能有点尴尬，告诉你为什么有时候你应该看看你面前的东西，而不是这些帖子:)

我之前运行了一个前向工程师，失败了，所以这意味着我的数据库已经有几个表，然后我一直坐着试图修复外键约束失败，试图确保一切都是完美的，但它运行到之前创建的表，所以它是没有获胜。

小开

检查两个表列上的签名。如果引用的表列是SIGNED，那么引用的表列也应该是SIGNED。

小开

导致此错误的另一个原因是当你的表或列包含保留关键字:

有时人们确实会忘记这些。

小开

在我的例子中，有一个语法错误，MySQL控制台在运行查询时没有显式地通知。然而，SHOW ENGINE INNODB STATUS命令的LATEST FOREIGN KEY ERROR节报告，

  Syntax error close to:


REFERENCES`role`(`id`) ON DELETE CASCADE) ENGINE = InnoDB DEFAULT CHARSET = utf8

我不得不在REFERENCES和role之间留下一个空白来使它工作。

小开
注意:下面的表格是我在做的时候从一些网站上截取的 数据库上的一些R&D。

对我来说，问题是，我的父表与我正在创建的表具有不同的字符集。

父表(PRODUCTS)

products | CREATE TABLE `products` (
`productCode` varchar(15) NOT NULL,
`productName` varchar(70) NOT NULL,
`productLine` varchar(50) NOT NULL,
`productScale` varchar(10) NOT NULL,
`productVendor` varchar(50) NOT NULL,
`productDescription` text NOT NULL,
`quantityInStock` smallint(6) NOT NULL,
`buyPrice` decimal(10,2) NOT NULL,
`msrp` decimal(10,2) NOT NULL,
PRIMARY KEY (`productCode`),
KEY `productLine` (`productLine`),
CONSTRAINT `products_ibfk_1` FOREIGN KEY (`productLine`) REFERENCES `productlines` (`productLine`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

有问题的子表

price_logs | CREATE TABLE `price_logs` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`productCode` varchar(15) DEFAULT NULL,
`old_price` decimal(20,2) NOT NULL,
`new_price` decimal(20,2) NOT NULL,
`added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `productCode` (`productCode`),
CONSTRAINT `price_logs_ibfk_1` FOREIGN KEY (`productCode`) REFERENCES `products` (`productCode`) ON DELETE CASCADE ON UPDATE CASCADE
);

修改为

price_logs | CREATE TABLE `price_logs` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`productCode` varchar(15) DEFAULT NULL,
`old_price` decimal(20,2) NOT NULL,
`new_price` decimal(20,2) NOT NULL,
`added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `productCode` (`productCode`),
CONSTRAINT `price_logs_ibfk_1` FOREIGN KEY (`productCode`) REFERENCES `products` (`productCode`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
小开

我的问题是，我试图在其他表之前创建关系表!

所以你有两种方法来解决它:

  • 改变MSQL命令的顺序

  • 在你的查询之前运行

    SET foreign_key_checks = 0;

小开

对我来说，它是-如果你为引用当前DB的非当前DB创建一个FK，你不能省略当前DB表的前缀:

USE currrent_db;
ALTER TABLE other_db.tasks ADD CONSTRAINT tasks_fk FOREIGN KEY (user_id) REFERENCES currrent_db.users (id);

如果我省略了users表的" current_db ."，我会得到FK错误。有趣的是显示引擎INNODB状态;在这个例子中什么都没有。

小开

我的解决方案! !

如果我们想让table1的columnn1作为table2的外键，那么columnn1应该是table1的键。

例如，假设我们有departments表，它有dept_id列。

现在，假设我们有另一个名为employees的表，它有emp_dept_id列。

如果我们想使用department表的dept_id列作为emp的emp_dept_id列的外键，那么department表至少应该是的dept_id如果不是主键，也是一个键。

因此，在将depratment的dept_id用作另一个表的外键之前，请确保它是一个主键或唯一键。

小开

如果你在PhpMyAdmin中得到这个错误，在导入SQL文件之前禁用外键检查。

enter image description here

小开
对我来说，目标表正在阻塞外键。 我必须在外键指向的表上设置自动递增(AI)

提交答案