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

本文翻译自：MySQL Error 1215: Cannot add foreign key constraint

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. 我试图在这里搜索答案，但是我发现的所有内容都说是将db引擎设置为Innodb或确保要用作外键的键是它们自己表中的主键。 I have done both of these things, if I'm not mistaken. 如果我没记错的话，我都做过这两件事。 Any other help you guys could offer? 你们还有其他帮助吗？

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

---

#1楼

参考：https://stackoom.com/question/19CQW/MySQL错误-无法添加外键约束

---

#2楼

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 . 我猜想Clients.Case_Number和/或Staff.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. 两个表中的数据类型必须完全相同。

---

#3楼

Reasons you may get a foreign key constraint error: 您可能会收到外键约束错误的原因：

1. You are not using InnoDB as the engine on all tables. 您没有将InnoDB用作所有表的引擎。
2. 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) 确保它是另一个表上的键 （可以是主键或唯一键）
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. 如果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. 原因之一也可能是您用于ON DELETE SET NULL的列未定义为null。 So make sure that the column is set default null. 因此，请确保将该列设置为默认null。

Check these. 检查这些。

---

#4楼

I got the same error while trying to add an fk. 尝试添加fk时遇到相同的错误。 In my case the problem was caused by the FK table's PK which was marked as unsigned. 在我的情况下，问题是由FK表的PK引起的，该PK被标记为未签名。

---

#5楼

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时，尤其是与JeffreyWay的Laravel 4 Generators一起使用时，“错误1215：无法添加外键约束”使我经历了一个陷阱。

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. 在Laravel 4中，您可以使用JeffreyWay的Generators生成迁移文件以一张一张地创建表，这意味着每个迁移文件都可以生成一张表。 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". 当您触发Artisan CLI命令“ php artisan migration”时，生成的顺序也是迁移操作的顺序。 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. 因此，如果文件要求引用将在后一个文件中生成但尚未生成的密钥的外键约束，则将引发错误1215。 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. 以正确的顺序生成新文件，复制内容，然后删除混乱的旧文件。

---

#6楼

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)

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 费利佩·泰尔西奥（FelipeTércio）