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

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?

我试图将我的新模式转发到我的db服务器上，但是我不知道为什么会出现这个错误。我试着在这里寻找答案，但是我找到的所有东西都说要么将db引擎设置为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

26 个解决方案

#1

I'm guessing that Clients.Case_Number and/or Staff.Emp_ID are not exactly the same data type as Clients_has_Staff.Clients_Case_Number and Clients_has_Staff.Staff_Emp_ID.

我猜客户。Case_Number和/或员工。Emp_ID与Clients_has_Staff不完全相同。Clients_Case_Number 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.

它们需要在两个表中使用完全相同的数据类型。

#2

Reasons you may get a foreign key constraint error:

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

1. You are not using InnoDB as the engine on all tables.
2. 您没有在所有表上使用InnoDB作为引擎。
3. You are trying to reference a nonexistent key on the target table. Make sure it is a key on the other table (it can be a primary or unique key)
4. 您正在尝试引用目标表上不存在的键。确保它是另一个表上的键(可以是主键或唯一键)
5. The types of the columns are not the same (exception is the column on the referencing table can be nullable).
6. 列的类型不相同(例外是引用表中的列可以为空)。
7. If the PK/FK is a varchar make sure the collation is the same for both.
8. 如果PK/FK是varchar，请确保两者的排序是相同的。

Update:

更新:

5. One of the reasons may also be that the column you are using for ON DELETE SET NULL is not defined to be null. So make sure that the column is set default null.
6. 其中一个原因可能是，您在DELETE SET NULL中使用的列没有定义为NULL。确保列设置为默认null。

Check these.

检查这些。

#3

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 foriegn键错误的信息

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.

无法在引用表中找到索引，其中引用列作为第一个列出现，或表中的列类型和引用表不匹配约束。

#4

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

错误1215是一个烦人的错误。“爆炸药丸”的回答涵盖了基本问题。你要确保从这里开始。然而，还有更多更微妙的情况需要注意:

For example, when you try to link up PRIMARY KEYs of different tables, make sure to provide proper ON UPDATE and ON DELETE options. E.g.:

例如，当您尝试链接不同表的主键时，请确保在更新和删除选项上提供适当的选项。例如:

...
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.

不会飞行，因为主键(如id)不能为空。

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!

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

#5

In my case, I had deleted a table using SET FOREIGN_KEY_CHECKS=0, then SET FOREIGN_KEY_CHECKS=1 after. 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.

在我的例子中，我使用setforeign_key_check =0删除了一个表，然后在后面设置foreign_key_check =1。当我重新加载表时，我得到了错误1215。问题是，数据库中还有一个表，它有一个外键指向我删除并正在重新加载的表。重新加载过程的一部分涉及到为其中一个字段更改数据类型，这使得来自另一个表的外键无效，从而触发错误1215。我通过删除问题解决了这个问题，然后用新的数据类型重新加载了相关字段的数据类型。

#6

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的生成器生成迁移文件来逐个创建表，这意味着每个迁移文件生成一个表。您必须意识到，每个迁移文件都是用文件名中的时间戳生成的，这将给文件一个命令。当您触发Artisan CLI命令“php Artisan migration”时，生成的顺序也是迁移操作的顺序。因此，如果一个文件请求一个外键约束，该外键约束指向一个将在后一个文件中生成但尚未生成的键，那么将触发错误1215。在这种情况下，您需要做的是调整迁移文件生成的顺序。以适当的顺序生成新文件，在内容中复制，然后删除无序的旧文件。

#7

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引起的，它被标记为无符号。

#8

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

检查表的排序，使用SHOW table状态可以检查表的信息，包括排序。

Both tables have to has the same collation.

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

It's happened to me.

这是发生在我身上。

#9

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)

我在主键(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

菲利普方阵上场

#10

I can not find this error

我找不到这个错误

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
)

#11

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

我希望这能有所帮助;

#12

This also happens when the type of the columns is not the same.

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

e.g. if the column you are referring to is UNSIGNED INT and the column being referred is INT then you get this error.

例如，如果所引用的列是无符号INT，而所引用的列是INT，那么您就会得到这个错误。

#13

For MySQL (INNODB) ... get definitions for columns you want to link

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

compare and verify both column definitions have

比较和验证两个列定义

same COLUMN_TYPE(length), same COLATION

相同的COLUMN_TYPE(长度),同样的过滤

could be helpfull to play like

玩得像这样有帮助吗

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;

#14

Check for table compatibility. For example, if one table is MyISAM and the other one is InnoDB, you may have this issue.

检查表的兼容性。例如，如果一个表是MyISAM，另一个表是InnoDB，您可能会遇到这个问题。

#15

Another reason: if you use ON DELETE SET NULL all columns that are used in the foreign key must allow null values. Someone else found this out in this question.

另一个原因是:如果您在删除集合中使用，那么在外键中使用的所有列都必须允许空值。有人在这个问题上发现了这个问题。

From my understanding it wouldn't be a problem regarding data integrity, but it seems that MySQL just doesn't support this feature (in 5.7).

根据我的理解，这并不是数据完整性的问题，但是MySQL似乎不支持这个特性(在5.7中)。

#16

When this error occurrs because the referenced table uses the MyISAM engine this answer provides a quick way to convert your database so all Django model tables use InnoDB: https://*.com/a/15389961/2950621

当这个错误发生时，因为引用的表使用MyISAM引擎，这个答案提供了一个快速转换数据库的方法，所以所有Django模型表都使用InnoDB: https://*.com/a/15389961/2950621。

It's a Django management command called convert_to_innodb.

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

#17

For me it was the column types. BigINT != INT.

对我来说是列类型。长整型数字! = INT。

But then it still didn't work.

但它还是不起作用。

So I checked the engines. Make sure Table1 = InnoDB and Table = InnoDB

所以我检查了引擎。确保表1 = InnoDB，表= InnoDB

#18

Even i had the same problem . And the fault was with the "unsigned" marker in the FK's table PK

甚至我也有同样的问题。错误在于FK表PK中的“无符号”标记

#19

Wooo I just got it ! It was a mix of a lot of already posted answers (innoDB, unsigned, etc). One thing I didn't see here though is : if your FK is pointing on a PK, ensure the source column has a value that makes sense. For example, if the PK is a mediumint(8), make sure the source column also contains a mediumint(8). That was part of the problem for me.

喔，我刚拿到!它混合了许多已经发布的答案(innoDB, unsigned，等等)。我在这里没有看到的是:如果您的FK指向一个PK，确保源列有一个有意义的值。例如，如果PK是mediumint(8)，请确保源列也包含mediumint(8)。对我来说，这是问题的一部分。

#20

Be aware of the use of backquotes too. I had in a script the following statement

还要注意后面引号的使用。我在脚本中有如下的语句

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

but the backquotes at the end were false. It should have been:

但是最后的引号是错误的。它应该是:

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

MySQL give unfortunalty no details on this error...

MySQL没有提供关于这个错误的细节。

#21

I experienced this error for a completely different reason. I used MySQL Workbench 6.3 for creating my Data Model (awesome tool). I noticed that when the column order defined in the Foreign Key constraint definition does not fit the table column sequence this error is also generated.

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

It took me about 4 hours of trying everything else but checking that.

我花了大约4个小时尝试了所有的东西，除了检查。

Now all is working well and I can get back to coding. :-)

现在一切都很顺利，我可以回到编码。:-)

#22

Another source of this error is that you have 2 or more same table names which have the same foreign key names. This sometimes happens to people who use modelling and design software, like Mysql Workbench, and later generate the script from the design.

此错误的另一个来源是您有两个或多个具有相同外键名的表名。这种情况有时发生在使用建模和设计软件的人身上，比如Mysql Workbench，然后从设计中生成脚本。

#23

when try to make foreign key when using laravel migration

当使用laravel迁移时，尝试使用外键。

like this example:

像这样的例子:

user table

用户表

    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();
    });
}

colors table

颜色表

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

sometimes properties didn't work

有时属性没有工作

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

this error happened because the foreign key (type) in [user table] is deferent from primary key (type) in [colors table]

之所以会出现此错误，是因为[user表]中的外键(type)与[colors表]中的主键(type)不同

To solve this problem should change the primary key in [colors table]

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

$table->tinyIncrements('id');

$表- > tinyIncrements(“id”);

---

When you use primary key $table->Increments('id');

当您使用主键$table->增量时('id');

you should use Integer as a foreign key

您应该使用Integer作为外键

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

---

When you use primary key $table->tinyIncrements('id');

当您使用主键$table->tinyIncrements('id');

you should use unsignedTinyInteger as a foreign key

您应该使用unsignedTinyInteger作为外键

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

---

When you use primary key $table->smallIncrements('id');

当您使用主键$table->小增量时('id');

you should use unsignedSmallInteger as a foreign key

您应该使用unsignedSmallInteger作为外键

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

---

When you use primary key $table->mediumIncrements('id');

当您使用主键$table->中介发光('id')时;

you should use unsignedMediumInteger as a foreign key

您应该使用unsignedmediteger作为外键。

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

#24

I know i am VERY late to the party but i want to put it out here so that it is listed.

我知道我很晚才来参加聚会，但我想把它放在这里，这样它就列出来了。

As well as all of the above advice for making sure that fields are identically defined, and table types also have the same collation, make sure that you don't make the rookie mistake of trying to link fields where data in the CHILD field is not already in the PARENT field. If you have data that is in the CHILD field that you have not already entered in to the PARENT field then that will cause this error. It's a shame that the error message is not a bit more helpful.

以及上述建议确保字段相同的定义,和表类型也有相同的排序,确保你不要试图链接的新秀错误领域中的数据字段不是孩子已经在父字段。如果在子字段中有尚未输入到父字段的数据，则会导致此错误。令人遗憾的是，错误消息没有一点帮助。

If you are unsure, then backup the table that has the Foreign Key, delete all the data and then try to create the Foreign Key. If successful then you what to do!

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

Good luck.

祝你好运。

#25

This is a subtle version of what has already been said, but in my instance, I had 2 databases (foo and bar). I created foo first and I didn't realize it referenced a foreign key in bar.baz (which wasn't created yet). When I tried to create bar.baz (without any foreign keys), I kept getting this error. After looking around for a while I found the foreign key in foo.

这是已经说过的内容的微妙版本，但是在我的例子中，我有两个数据库(foo和bar)。我首先创建了foo，我没有意识到它在bar中引用了一个外键。baz(还没有创建)当我尝试创建bar的时候。baz(没有任何外键)，我一直得到这个错误。环顾四周，我找到了foo中的外键。

So, long story short, If you get this error, you may have a pre-existing foreign key to the table being created.

因此，长话短说，如果您得到了这个错误，您可能会有一个已经存在的外键到正在创建的表中。

#26

I had the same error once. I just simply restarted the MySQL server and fixed the problem.

我也犯过一次同样的错误。我只是重新启动MySQL服务器并修复了问题。