MySQL错误1215:不能添加外键约束

我试图将我的新模式转发到我的数据库服务器上，但我不知道为什么我会得到这个错误。

我试图在这里寻找答案，但我所找到的一切都表明，要么将数据库引擎设置为InnoDB，要么确保我试图用作外键的键是它们自己表中的主键。如果我没记错的话，这两件事我都做过。我还能做什么?

Executing SQL script in server


ERROR: Error 1215: Cannot add foreign key constraint


-- -----------------------------------------------------
-- Table `Alternative_Pathways`.`Clients_has_Staff`
-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `Alternative_Pathways`.`Clients_has_Staff` (
`Clients_Case_Number` INT NOT NULL ,
`Staff_Emp_ID` INT NOT NULL ,
PRIMARY KEY (`Clients_Case_Number`, `Staff_Emp_ID`) ,
INDEX `fk_Clients_has_Staff_Staff1_idx` (`Staff_Emp_ID` ASC) ,
INDEX `fk_Clients_has_Staff_Clients_idx` (`Clients_Case_Number` ASC) ,
CONSTRAINT `fk_Clients_has_Staff_Clients`
FOREIGN KEY (`Clients_Case_Number` )
REFERENCES `Alternative_Pathways`.`Clients` (`Case_Number` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Clients_has_Staff_Staff1`
FOREIGN KEY (`Staff_Emp_ID` )
REFERENCES `Alternative_Pathways`.`Staff` (`Emp_ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB

SQL脚本执行完成:语句:7成功，1失败

下面是父表的SQL。

CREATE  TABLE IF NOT EXISTS `Alternative_Pathways`.`Clients` (
`Case_Number` INT NOT NULL ,
`First_Name` CHAR(10) NULL ,
`Middle_Name` CHAR(10) NULL ,
`Last_Name` CHAR(10) NULL ,
`Address` CHAR(50) NULL ,
`Phone_Number` INT(10) NULL ,
PRIMARY KEY (`Case_Number`) )
ENGINE = InnoDB


CREATE  TABLE IF NOT EXISTS `Alternative_Pathways`.`Staff` (
`Emp_ID` INT NOT NULL ,
`First_Name` CHAR(10) NULL ,
`Middle_Name` CHAR(10) NULL ,
`Last_Name` CHAR(10) NULL ,
PRIMARY KEY (`Emp_ID`) )
ENGINE = InnoDB
568322 次浏览
小开
最佳答案

我猜Clients.Case_Number和/或Staff.Emp_ID与Clients_has_Staff.Clients_Case_Number和Clients_has_Staff.Staff_Emp_ID不是完全相同的数据类型。

也许父表中的列是INT UNSIGNED?

两个表中的数据类型必须完全相同。

小开

你可能会得到外键约束错误的原因:

  1. 你不是在所有表上都使用InnoDB作为引擎。
  2. 您正在尝试引用目标表上不存在的键。确保它是另一个表上的关键(它可以是一个主键或唯一键，或者只是一个key)
  3. 列的类型不相同(一个例外是引用表中的列可以为空，即使它在引用表中不为空)。
  4. 如果主键或外键是varchar，请确保两者的排序规则相同。
  5. 其中一个原因可能是你用于ON DELETE SET NULL的列没有被定义为空。所以确保列设置为默认空。

检查这些。

小开

在尝试添加外键时，我得到了同样的错误。在我的例子中，问题是由外键表的主键标记为unsigned引起的。

小开

我遇到过一个“错误1215:不能添加外键约束”的陷阱;当使用Laravel 4，特别是与JeffreyWay的Laravel 4生成器。

在Laravel 4中，您可以使用JeffreyWay的Generators生成迁移文件，逐个创建表，这意味着每个迁移文件生成一个表。

您必须意识到这样一个事实，即每个迁移文件在文件名中都有一个时间戳，它给出了文件的顺序。生成的顺序也是你触发Artisan CLI命令php artisan migrate时迁移操作的顺序。

因此，如果一个文件请求一个指向后一个文件中将要生成但尚未生成的键的外键约束，错误1215将被触发。

在这种情况下，您需要调整迁移文件生成的顺序。按正确顺序生成新文件，复制内容，然后删除无序的旧文件。

小开

我也有同样的问题。

我是这样解决的:

中创建了下面的行 primary key: (id int(11) unsigned NOT NULL AUTO_INCREMENT) < / p >

在尝试在我的模式构建器中导入一个表之后，我找到了这个解决方案。

小开

在我的例子中，我用SET FOREIGN_KEY_CHECKS=0删除了一个表，然后用SET FOREIGN_KEY_CHECKS=1删除了一个表。当我重新加载表时，我得到error 1215。问题是在数据库中有另一个表，有一个外键到我已经删除的表，并正在重新加载。重新加载过程的一部分涉及更改其中一个字段的数据类型，这使得来自另一个表的外键无效，从而触发error 1215。我解决了这个问题，方法是删除并重新加载另一个表，使用涉及字段的新数据类型。

小开

对于其他人，相同的错误可能并不总是由于列类型不匹配。您可以通过发出这个命令找到关于MySQL外键错误的更多信息

SHOW ENGINE INNODB STATUS;

您可能会在打印的消息顶部附近发现一个错误。类似的

无法在引用表中找到索引 引用的列显示为第一个列或列类型

.在表和引用的表中不匹配约束
小开

我找不到这个错误

CREATE TABLE RATING (


Riv_Id INT(5),
Mov_Id INT(10) DEFAULT 0,
Stars INT(5),
Rating_date DATE,


PRIMARY KEY (Riv_Id, Mov_Id),


FOREIGN KEY (Riv_Id) REFERENCES REVIEWER(Reviewer_ID)
ON DELETE SET NULL ON UPDATE CASCADE,


FOREIGN KEY (Mov_Id) REFERENCES MOVIE(Movie_ID)
ON DELETE SET DEFAULT ON UPDATE CASCADE
)
小开

错误1215是一个恼人的错误。爆炸药丸的答案涵盖了基础。你要确保从那里开始。然而，还有更多更微妙的情况需要注意:

例如，当你试图连接不同表的主键时，确保提供适当的ON UPDATE和ON DELETE选项。例如:

...
PRIMARY KEY (`id`),
FOREIGN KEY (`id`) REFERENCES `t` (`other_id`) ON DELETE SET NULL
....

不会生效，因为主键(比如id)不能是NULL。

我确信，在添加这类约束时，甚至还有更多类似的微妙问题，这就是为什么当遇到约束错误时，总是要确保约束及其含义在当前上下文中有意义。祝你的错误1215好运!

小开

当这个错误发生时，因为引用的表使用MyISAM引擎，这个答案提供了一个快速转换数据库的方法，这样所有的Django模型表都使用InnoDB: 使用Django将现有的MyISAM数据库转换为InnoDB

这是一个名为convert_to_innodb的Django管理命令。

小开

我也遇到过同样的问题，我的解决方案是:

之前:

CREATE TABLE EMPRES
( NoFilm smallint NOT NULL


PRIMARY KEY (NoFilm)


FOREIGN KEY (NoFilm) REFERENCES cassettes


);

解决方案:

CREATE TABLE EMPRES
(NoFilm smallint NOT NULL REFERENCES cassettes,


PRIMARY KEY (NoFilm)


);
小开

喔，我刚明白!这是许多已经发布的答案(InnoDB, unsigned等)的混合。

但我在这里没有看到的一点是:如果外键指向主键，请确保源列具有有意义的值。例如，如果主键是一个mediumint(8)，请确保源列也包含一个mediumint(8)。这是我的问题之一。

小开

当列的类型不相同时也会发生这种情况。

例如，如果你引用的列是一个UNSIGNED INT，而被引用的列是INT，那么你会得到这个错误。

小开

也要注意反节拍音的使用。我在一个脚本中有如下语句

ALTER TABLE service ADD FOREIGN KEY (create_by) REFERENCES `system_user(id)`;

但最后的反节拍是假的。它应该是:

ALTER TABLE service ADD FOREIGN KEY (create_by) REFERENCES `system_user`(`id`);

不幸的是MySQL没有给出这个错误的任何细节…

小开

对我来说是列类型。BigINT = INT。

但后来还是没起作用。

所以我检查了引擎。确保Table1 = InnoDB和Table = InnoDB

小开

我经历这个错误的原因完全不同。我使用MySQL工作台 6.3来创建我的数据模型(很棒的工具)。我注意到，当外键约束定义中定义的列顺序不符合表列顺序时，也会生成此错误。

我花了大约4个小时的时间尝试了所有其他方法，就是没能确认。

现在一切正常，我可以继续编码了。: -)

小开

此错误的另一个来源是当您有两个或多个具有相同外键名的相同表名时。

这种情况有时会发生在那些使用建模和设计软件的人身上，比如MySQL工作台，然后从设计中生成脚本。

小开

MySQL (InnoDB)…获取想要链接的列的定义:

SELECT * FROM information_schema.columns WHERE
TABLE_NAME IN (tb_name','referenced_table_name') AND
COLUMN_NAME  IN ('col_name','referenced_col_name')\G

比较并验证两个列定义具有:

相同的COLUMN_TYPE(长度)，相同的COLATION

可能需要禁用/启用foreign_key机制，但要注意在生产环境中:

set foreign_key_checks=0;
ALTER TABLE tb_name ADD FOREIGN KEY(col_name) REFERENCES ref_table(ref_column) ON DELETE ...
set foreign_key_checks=1;
小开

检查表(引擎)与SHOW TABLE STATUS WHERE Name = 'tableName'的兼容性。

例如，如果一个表是MyISAM，另一个表是InnoDB，你可能会遇到这个问题。

你可以通过下面的命令来改变它:

ALTER TABLE myTable ENGINE = InnoDB;

从文档。

小开

另一个原因是:如果你使用ON DELETE SET NULL，在外键中使用的所有列必须允许空值。其他人在这个问题中发现了这一点。

从我的理解，这不会是一个关于数据完整性的问题，但似乎MySQL只是不支持这个功能(在5.7)。

小开

当使用Laravel迁移时尝试创建外键时，就像下面的例子:

用户表

public function up()
{
Schema::create('flights', function (Blueprint $table) {
$table->increments('id');
$table->string('name');
$table->TinyInteger('color_id')->unsigned();
$table->foreign('color_id')->references('id')->on('colors');
$table->timestamps();
});
}

颜色表

public function up()
{
Schema::create('flights', function (Blueprint $table) {
$table->increments('id');
$table->string('color');
$table->timestamps();
});
}

有时候属性不起作用:

[PDOException]
SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

发生此错误是因为[用户表]中的外键(类型)与[颜色表]中的主键(类型)不同。

要解决这个问题，你应该改变[colors table]中的主键:

$table->tinyIncrements('id');

当你使用主键$table->Increments('id');时，你应该使用Integer作为外键:

$table->unsignedInteger('fk_id');
$table->foreign('fk_id')->references('id')->on('table_name');

当你使用主键$table->tinyIncrements('id');时，你应该使用unsignedTinyInteger作为外键:

$table->unsignedTinyInteger('fk_id');
$table->foreign('fk_id')->references('id')->on('table_name');

当你使用主键$table->smallIncrements('id');时，你应该使用unsignedSmallInteger作为外键:

$table->unsignedSmallInteger('fk_id');
$table->foreign('fk_id')->references('id')->on('table_name');

当你使用主键$table->mediumIncrements('id');时，你应该使用unsignedMediumInteger作为外键:

$table->unsignedMediumInteger('fk_id');
$table->foreign('fk_id')->references('id')->on('table_name');
小开

正如前面所有关于确保字段定义相同以及表类型也具有相同排序规则的建议一样，请确保不要犯新手错误，即试图链接孩子字段中的数据不在父字段中的字段。如果你在孩子字段中有数据，而你还没有在父字段中输入，那么这将导致此错误。遗憾的是，错误信息一点帮助都没有。

如果您不确定，那么备份具有外键的表，删除所有数据，然后尝试创建外键。如果成功了，你就知道该怎么做了!

小开

这是前面所说内容的微妙版本，但在我的实例中，我有2个数据库(foo和bar)。我首先创建了foo，我没有意识到它在酒吧引用了一个外键。Baz(尚未创建)。当我尝试创建bar的时候。baz(没有任何外键)，我一直得到这个错误。在四处寻找了一会儿之后，我在foo中找到了外键。

所以，长话短说，如果您得到这个错误，您可能有一个预先存在的外键到正在创建的表。

小开

检查表格的排列顺序。使用SHOW TABLE STATUS，你可以检查关于表的信息，包括排序规则。

两个表必须具有相同的排序规则。

这种事发生在我身上。

小开

在我的情况下，我不得不禁用FOREIGN KEY检查，因为源表不存在。

SET FOREIGN_KEY_CHECKS=0;

小开

我只是想为VARCHAR外键关系添加这种情况。我花了上周的时间在MySQL Workbench 8.0中试图解决这个问题，最终能够修复这个错误。

< >强简短的回答: 模式、表、列、引用表、引用列和引用父表的任何其他表的字符集和排序规则必须匹配。< / p >

< >强劲长答: 我的表中有一个ENUM数据类型。我将其更改为VARCHAR，并且我可以从引用表中获取值，这样我就不必修改父表来添加额外的选项。这个外键关系看起来很简单，但我得到了1215错误。arvind的答案和后面的链接建议使用

SHOW ENGINE INNODB STATUS;

在使用这个命令时，我得到了以下详细的错误描述，没有其他有用的信息

无法在引用表中找到索引 引用的列显示为第一个列或列类型 在表和引用表中不匹配约束。 注意，ENUM和SET的内部存储类型在 用>= InnoDB-4.1.12创建的表，以及旧表中的这些列 不能被新表中的此类列引用。 正确的外键定义请参考http://dev.mysql.com/doc/refman/8.0/en/innodb-foreign-key-constraints.html

之后，我使用SET FOREIGN_KEY_CHECKS=0;作为阿尔温德•巴拉和链接在这里的建议:

这给出了以下错误信息:

错误码:1822。日志含义添加外键约束失败。失踪 约束

的索引

在这一点上，我对模式进行了“逆向工程”，并且能够在EER图中创建外键关系。在'forward engineer'-ing上，我得到了以下错误:

错误1452:不能添加或更新子行:外键约束 失败< / p >

当我将EER图“转发工程”到一个新模式时，SQL脚本运行时没有出现问题。通过比较转发工程师尝试生成的SQL，我发现不同之处在于字符集和排序规则。父表、子表和两列具有utf8mb4字符集和utf8mb4_0900_ai_ci排序规则，然而，父表中的另一列使用CHARACTER SET = utf8 , COLLATE = utf8_bin ;引用到另一个子表。

对于整个模式，我更改了所有表和所有列的字符集和排序规则如下:

CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

这最终解决了我1215错误的问题。

< >强注: 排序utf8mb4_general_ci适用于MySQL Workbench 5.0或更高版本。排序utf8mb4_0900_ai_ci只适用于MySQL Workbench 8.0或更高版本。我相信我遇到字符集和排序问题的原因之一是由于MySQL Workbench升级到8.0之间。下面是一个链接，它讨论了更多关于这个排序规则的内容

小开

对我来说，1215错误发生在我导入mysqldump创建的dumpfile时，它按字母顺序创建表，在我的情况下，导致外键引用文件中稍后创建的表。(这篇博客文章指出了这一点:MySQL Error Code 1215: " Cannot add foreign key constraint " .)

由于mysqldump按字母顺序排列表，我不想改变表的名称，我遵循了JeremyWeir 在本页回答中的说明，这说明将set FOREIGN_KEY_CHECKS = 0;放在转储文件的顶部，并将SET FOREIGN_KEY_CHECKS = 1;放在转储文件的底部。

这个方法对我很有效。

小开

所以我尝试了以上所有的修复，但运气不好。我可能错过了我的表中的错误-只是找不到原因，我一直得到错误1215。所以我用了这个方法。

在phpMyAdmin的本地环境中，我导出了相关表中的数据。我选择了CSV格式。虽然仍然在phpMyAdmin与表选择，我选择“更多->选项”。在这里，我向下滚动到“复制表到(database.table)”。选择“仅结构”。重命名表，也许只是在当前表名旁边添加单词“copy”。点击“Go”，这将创建一个新表。导出新表并将其导入到新服务器或其他服务器。我在这里也使用phpMyAdmin。一旦导入，将表的名称更改为其原始名称。选择新表，选择import。格式选择CSV。取消选中“启用外键检查”。选择“走”。到目前为止一切都很好。

我在博客上发布了我的修正。

小开

你也可以检查两个表的Engine设置为InnoDB。


提交答案