MySQL 使用外键创建表给出 errno: 150

Question

我试图在 MySQL 中创建一个带有两个外键的表，这两个外键引用另外 2 个表中的主键，但是我收到了 errno: 150 错误，并且它不会创建该表。

以下是所有 3 个表的 SQL：

CREATE TABLE role_groups (
  `role_group_id` int(11) NOT NULL `AUTO_INCREMENT`,
  `name` varchar(20),
  `description` varchar(200),
  PRIMARY KEY (`role_group_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `roles` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50),
  `description` varchar(200),
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB;

create table role_map (
  `role_map_id` int not null `auto_increment`,
  `role_id` int not null,
  `role_group_id` int not null,
  primary key(`role_map_id`),
  foreign key(`role_id`) references roles(`role_id`),
  foreign key(`role_group_id`) references role_groups(`role_group_id`)
) engine=InnoDB;

Answer 1

必须满足这些条件，才能避免在 ALTER TABLE ADD FOREIGN KEY 中出现错误 150：

1. 在定义外键来引用它之前，父表必须存在。您必须按正确的顺序定义表：首先是父表，然后是子表。如果两个表相互引用，则必须创建一个没有 FK 约束的表，然后创建第二个表，然后使用 ALTER TABLE 将 FK 约束添加到第一个表。

2. 这两个表必须都支持外键约束，即ENGINE=InnoDB。其他存储引擎默默地忽略外键定义，因此它们不会返回错误或警告，但不会保存 FK 约束。

3. 父表中引用的列必须是键的最左边的列。如果父项中的键是 PRIMARY KEY 或 UNIQUE KEY，则最好。

4. FK 定义必须以与 PK 定义相同的顺序引用 PK 列。例如，如果 FK REFERENCES Parent(a,b,c)，则不得按 (a,c,b) 的顺序在列上定义 Parent 的 PK。< /p>

5. 父表中的 PK 列必须与子表中的 FK 列具有相同的数据类型。例如，如果父表中的 PK 列是 UNSIGNED，请务必为子表字段中的相应列定义 UNSIGNED。

   例外：字符串的长度可能不同。例如，VARCHAR(10) 可以引用 VARCHAR(20)，反之亦然。

6. 任何字符串类型的 FK 列必须与相应的 PK 列具有相同的字符集和排序规则。

7. 如果子表中已有数据，则 FK 列中的每个值都必须与父表 PK 列中的值匹配。使用如下查询检查这一点：

    SELECT COUNT(*) FROM Child LEFT OUTER JOIN Parent ON Child.FK = Parent.PK 
    WHERE Parent.PK 为 NULL；
   

   这必须返回零 (0) 个不匹配的值。显然，这个查询是一个通用的例子；您必须替换表名和列名。

8. 父表和子表都不能是TEMPORARY表。

9. 父表和子表都不能是 PARTITIONED 表。

10. 如果使用 ON DELETE SET NULL 选项声明 FK，则 FK 列必须可为空。

11. 如果为外键声明约束名称，则该约束名称在整个架构中必须是唯一的，而不仅仅是在定义该约束的表中唯一。两个表不能有自己的同名约束。

12. 如果其他表中有任何其他 FK 指向您尝试为其创建新 FK 的同一字段，并且它们格式错误（即不同的排序规则），则需要首先使它们保持一致。这可能是由于过去的更改造成的，其中 SET FOREIGN_KEY_CHECKS = 0; 被错误地定义为不一致的关系。有关如何识别这些问题 FK 的说明，请参阅下面 @andrewdotn 的回答。

Answer 2

MySQL 的通用“errno 150”消息“意味着外键约束未正确形成。”如果您正在阅读此页面，您可能已经知道，通用的“errno：150”错误消息确实没有帮助。但是：

您可以通过运行 SHOW ENGINE INNODB STATUS; 获取实际错误消息；，然后在

例如，创建外键约束的尝试：

CREATE TABLE t1
(id INTEGER);

CREATE TABLE t2
(t1_id INTEGER,
 CONSTRAINT FOREIGN KEY (t1_id) REFERENCES t1 (id));

失败，并出现错误 Can't create table 'test.t2' (errno: 150)。除了这是一个外键问题之外，这并没有告诉任何人任何有用的信息。但是运行SHOW ENGINE INNODB STATUS;它会说：

------------------------
LATEST FOREIGN KEY ERROR
------------------------
130811 23:36:38 Error in foreign key constraint of table test/t2:
FOREIGN KEY (t1_id) REFERENCES t1 (id)):
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.

它说问题是找不到索引。 SHOW INDEX FROM t1 显示表 t1 根本没有任何索引。例如，通过在 t1 上定义主键来解决此问题，并且外键约束将成功创建。

Answer 3

确保您尝试使用约束链接的两个字段的属性完全相同。

通常，ID 列上的“无符号”属性会让您感到困惑。

ALTER TABLE `dbname`.`tablename` CHANGE `fieldname` `fieldname` int(10) UNSIGNED NULL;

Answer 4

运行此脚本时数据库的当前状态是什么？它完全是空的吗？从头开始创建数据库时，您的 SQL 对我来说运行良好，但 errno 150 通常与删除 & 有关。重新创建作为外键一部分的表。我感觉您没有使用 100% 全新的数据库。

如果您在“source”SQL 文件时出错，您应该能够在“source”命令之后立即从 MySQL 提示符运行“SHOW ENGINE INNODB STATUS”命令来查看更详细的错误信息。

您可能还想查看手动输入：

如果重新创建已删除的表，它必须具有符合引用它的外键约束的定义。如前所述，它必须具有正确的列名称和类型，并且必须在引用的键上具有索引。如果不满足这些条件，MySQL 将返回错误号 1005 并在错误消息中引用错误 150。如果 MySQL 从 CREATE TABLE 语句报告错误号 1005，并且错误消息引用错误 150，则表创建失败，因为外键约束未正确形成。

— MySQL 5.1 参考手册.

Answer 5

对于正在查看此帖子并遇到同样问题的人：

出现此类错误的原因有很多。有关 MySQL 中外键错误的原因和解决方案的相当完整的列表（包括此处讨论的那些），请查看此链接：

MySQL 外键错误和错误号150

Answer 6

对于通过 Google 找到此 SO 条目的其他人：请确保您没有尝试对定义为“NOT NULL”的外键（待）列执行 SET NULL 操作。这引起了极大的挫败感，直到我记得检查引擎 INNODB 状态。

Answer 7

绝对不是这样的，但我发现这个错误很常见而且不明显。 FOREIGN KEY 的目标可能不是 PRIMARY KEY。对我有用的答案是：

外键始终必须指向其他表的主键真实字段。

CREATE TABLE users(
   id INT AUTO_INCREMENT PRIMARY KEY,
   username VARCHAR(40));

CREATE TABLE userroles(
   id INT AUTO_INCREMENT PRIMARY KEY,
   user_id INT NOT NULL,
   FOREIGN KEY(user_id) REFERENCES users(id));

Answer 8

正如 @andrewdotn 所指出的，最好的方法是查看详细的错误（SHOW ENGINE INNODB STATUS;），而不仅仅是错误代码。

原因之一可能是同名索引已存在，可能位于另一个表中。作为实践，我建议在索引名称之前添加表名称前缀以避免此类冲突。例如，使用 idx_userActionMapping_userId 代替 idx_userId。

Answer 9

请首先确保

1. 您使用的是 InnoDB 表。
2. FOREIGN KEY 字段与源字段具有相同的类型和长度 (!)。

我也遇到了同样的麻烦，并且已经解决了。我对一个字段有无符号 INT，而对其他字段只有整数。

Answer 10

有用的提示，在尝试 CREATE 查询后使用 SHOW WARNINGS; ，您将收到错误以及更详细的警告：

    ---------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                                 |
+---------+------+--------------------------------------------------------------------------                          --------------------------------------------------------------------------------------------                          ---------------+
| Warning |  150 | Create table 'fakeDatabase/exampleTable' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
|
| Error   | 1005 | Can't create table 'exampleTable' (errno:150)                                                                                                                                                                           |
+---------+------+--------------------------------------------------------------------------                          --------------------------------------------------------------------------------------------                          ---------------+

因此在这种情况下，需要重新创建我的桌子！

Answer 11

当您尝试将文件源导入现有数据库时，通常会发生这种情况。
首先删除所有表（或数据库本身）。
然后源文件的开头为 SETforeign_key_checks = 0; ，结尾为 SETforeign_key_checks = 1; 。

Answer 12

我发现了失败的另一个原因......区分大小写的表名。

对于这个表定义

CREATE TABLE user (
  userId int PRIMARY KEY AUTO_INCREMENT,
  username varchar(30) NOT NULL
) ENGINE=InnoDB;

这个表定义有效

CREATE TABLE product (
  id int PRIMARY KEY AUTO_INCREMENT,
  userId int,
  FOREIGN KEY fkProductUser1(userId) REFERENCES **u**ser(userId)
) ENGINE=InnoDB;

，而这个失败

CREATE TABLE product (
  id int PRIMARY KEY AUTO_INCREMENT,
  userId int,
  FOREIGN KEY fkProductUser1(userId) REFERENCES User(userId)
) ENGINE=InnoDB;

它在 Windows 上有效但在 Unix 上失败这一事实花了我几个小时才弄清楚。希望对其他人有帮助。

Answer 13

适用于 Mac 操作系统的 MySQL Workbench 6.3。

问题：尝试在数据库图上进行正向工程时，表 X 上出现 errno 150，21 次中有 20 次成功，1 次失败。如果表 X 上的 FK 被删除，则错误会转移到之前未发生故障的另一个表。

将所有表引擎更改为 myISAM，它工作得很好。

Answer 14

还值得检查一下您是否意外地操作了错误的数据库。如果外表不存在，就会出现此错误。为什么 MySQL 必须如此神秘？

Answer 15

确保外键在父级中未列为唯一。我遇到了同样的问题，我通过将其划分为非唯一来解决它。

Answer 16

就我而言，这是由于作为外键字段的字段名称太长，即。 外键 (some_other_table_with_long_name_id)。尝试更短一些。在这种情况下，错误消息有点误导。

另外，正如 @Jon 之前提到的 - 字段定义必须相同（注意 unsigned 子类型）。

Answer 17

（旁注对于评论来说太大）

映射表中不需要 AUTO_INCRMENT id；摆脱它。

将 PRIMARY KEY 更改为 (role_id, role_group_id)（任意顺序）。这将使访问速度更快。

由于您可能想要映射两个方向，因此还要添加一个 INDEX ，其中这两列的顺序相反。 （无需使其UNIQUE。）

更多提示：http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

Answer 18

当外键约束基于 varchar 类型时，则除了 marv 提供的 列表-el目标列必须具有唯一约束。

Answer 19

在创建表之前执行以下行：
设置 FOREIGN_KEY_CHECKS = 0;

FOREIGN_KEY_CHECKS 选项指定是否检查 InnoDB 表的外键约束。

-- 指定检查外键约束（这是默认值

SET FOREIGN_KEY_CHECKS = 1;

）

-- 不检查外键约束

SET FOREIGN_KEY_CHECKS = 0;

何时使用：
当您需要重新创建表并以任何父子顺序加载数据时，暂时禁用引用约束（将 FOREIGN_KEY_CHECKS 设置为 0）非常有用

Answer 20

我遇到了同样的问题，但我检查发现我没有父表。所以我只是在子迁移之前编辑父迁移。去做就对了。