MySQL 错误 1215：无法添加外键约束

MySQL Error 1215: Cannot add foreign key constraint

提问by Robert B

I am trying to forward engineer my new schema onto my db server, but I can't figure out why I am getting this error. I've tried to search for the answer here, but everything I've found has said to either set the db engine to Innodb or to make sure the keys I'm trying to use as a foreign key are primary keys in their own tables. I have done both of these things, if I'm not mistaken. Any other help you guys could offer?

我正在尝试将我的新模式转发到我的数据库服务器上，但我不知道为什么我会收到这个错误。我试图在这里搜索答案，但我发现的所有内容都说要么将数据库引擎设置为 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 script execution finished: statements: 7 succeeded, 1 failed

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

Here is the SQL for the parent tables.

这是父表的 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

回答by Ike Walker

I'm guessing that Clients.Case_Numberand/or Staff.Emp_IDare not exactly the same data type as Clients_has_Staff.Clients_Case_Numberand Clients_has_Staff.Staff_Emp_ID.

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

Perhaps the columns in the parent tables are INT UNSIGNED?

也许父表中的列是INT UNSIGNED？

They need to be exactly the same data type in both tables.

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

回答by Explosion Pills

Reasons you may get a foreign key constraint error:

您可能会收到外键约束错误的原因：

1. You are not using InnoDB as the engine on all tables.
2. You are trying to reference a nonexistent key on the target table. Make sure it is a keyon the other table (it can be a primary or unique key)
3. The types of the columns are not the same (exception is the column on the referencing table can be nullable).
4. If the PK/FK is a varchar make sure the collation is the same for both.

1. 您没有使用 InnoDB 作为所有表的引擎。
2. 您正在尝试引用目标表上不存在的键。确保它是另一个表上的键（它可以是主键或唯一键）
3. 列的类型不同（引用表上的列可以为空除外）。
4. 如果 PK/FK 是 varchar，请确保两者的排序规则相同。

Update:

更新：

1. One of the reasons may also be that the column you are using for ON DELETE SET NULLis not defined to be null. So make sure that the column is set default null.

5. 原因之一也可能是您使用的列ON DELETE SET NULL未定义为空。因此，请确保将列设置为默认空值。

Check these.

检查这些。

回答by arvind

For others same error may not always be due to column type mismatch, you can find out more information about a mysql foriegn key error by issuing command

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

SHOW ENGINE INNODB STATUS;

you may find a error near the top of the printed message something like

您可能会在打印消息的顶部附近发现错误，例如

Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint.

无法在被引用表中找到被引用列显示为第一列的索引，或者表中的列类型与被引用表的约束不匹配。

回答by Domi

Error 1215 is an annoying one. Explosion Pill's answercovers the basics. You want to make sure to start from there. However, there are more, much more subtle cases to look out for:

错误 1215 是一个令人讨厌的错误。Explosion Pill 的回答涵盖了基础知识。你想确保从那里开始。然而，还有更多、更微妙的情况需要注意：

For example, when you try to link up PRIMARY KEYs of different tables, make sure to provide proper ON UPDATEand ON DELETEoptions. E.g.:

例如，当您尝试链接不同表的 PRIMARY KEY 时，请确保提供适当的ON UPDATE和ON DELETE选项。例如：

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

won't fly, because PRIMARY KEYs (such as id) can't be NULL.

不会飞，因为 PRIMARY KEY（例如id）不能是NULL.

I am sure, there are even more, similarly subtle issues when adding these sort of constraints, which is why when coming across constraint errors, always make sure that the constraints and their implications make sense in your current context. Good luck with your error 1215!

我敢肯定，在添加这些类型的约束时，还有更多类似的微妙问题，这就是为什么在遇到约束错误时，始终确保约束及其含义在您当前的上下文中有意义。祝你的错误 1215 好运！

回答by Carlos Laspina

Check the collation of table, using SHOW TABLE STATUSyou can check information about the tables, including the collation.

检查表的排序规则，使用SHOW TABLE STATUS可以检查有关表的信息，包括排序规则。

Both tables have to has the same collation.

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

It's happened to me.

它发生在我身上。

回答by CodeMed

In my case, I had deleted a table using SET FOREIGN_KEY_CHECKS=0, then SET FOREIGN_KEY_CHECKS=1after. When I went to reload the table, I got error 1215. The problem was there was another table in the database that had a foreign key to the table I had deleted and was reloading. Part of the reloading process involved changing a data type for one of the fields, which made the foreign key from the other table invalid, thus triggering error 1215. I resolved the problem by dropping and then reloading the other table with the new data type for the involved field.

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

回答by user2975399

There is a pitfall I have experienced with "Error 1215: Cannot add foreign key constraint" when using Laravel 4, especially with JeffreyWay's Laravel 4 Generators.

使用 Laravel 4 时，我遇到了“错误 1215：无法添加外键约束”的陷阱，尤其是使用 JeffreyWay 的 Laravel 4 生成器时。

In Laravel 4, you can use JeffreyWay's Generators to generate migration files to create tables one-by-one, which means, each migration file generates one table. You have to be aware of the fact that each migration file is generated with a timestamp in the filename, which gives the files an order. The order of generation is also the order of migration operation when you fire the Artisan CLI command "php artisan migrate". So, if a file asks for a foreign key constraint referring to a key which will be, but not yet, generated in a latter file, the Error 1215 is fired. In such case, what you have to do is adjust the order of migration files generation. Generate new files in proper order, copy-in the content, then delete the disordered old files.

在 Laravel 4 中，您可以使用 JeffreyWay 的 Generators 生成迁移文件来一张一张地创建表，这意味着每个迁移文件生成一张表。您必须注意这样一个事实，即每个迁移文件都是在文件名中带有时间戳的，这为文件提供了一个顺序。当您触发 Artisan CLI 命令“php artisan migrate”时，生成顺序也是迁移操作的顺序。因此，如果文件要求引用将在后一个文件中生成但尚未生成的键的外键约束，则会触发错误 1215。在这种情况下，您需要做的是调整迁移文件生成的顺序。按正确顺序生成新文件，复制内容，然后删除无序的旧文件。

回答by Alex

I got the same error while trying to add an fk. In my case the problem was caused by the FK table's PK which was marked as unsigned.

我在尝试添加 fk 时遇到了同样的错误。在我的情况下，问题是由标记为未签名的 FK 表的 PK 引起的。

回答by user3478348

I had the same problem.
I solved it doing this:

我有同样的问题。
我解决了它这样做：

I created the following line in the
primary key: (id int(11) unsigned NOT NULL AUTO_INCREMENT)

我在
primary key: (id int(11) unsigned NOT NULL AUTO_INCREMENT)

I found out this solution after trying to import a table in my schema builder. If it works for you, let me know!

在尝试在我的架构构建器中导入一个表后，我发现了这个解决方案。如果它对你有用，请告诉我！

Good luck!

祝你好运！

Felipe Tércio

费利佩·泰尔西奥

回答by Yacine Richthofen

i had the same issue, my solution:

我有同样的问题，我的解决方案：

Before:

前：

CREATE TABLE EMPRES
( NoFilm smallint NOT NULL

  PRIMARY KEY (NoFilm)

  FOREIGN KEY (NoFilm) REFERENCES cassettes

);

Solution:

解决方案：

CREATE TABLE EMPRES
(NoFilm smallint NOT NULL REFERENCES cassettes,

 PRIMARY KEY (NoFilm)

);

I hope it's help ;)

我希望它有帮助;)