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. 父表中的引用列必须是键的最左侧列。最好是 Parent 中的键是 PRIMARY KEY 或 UNIQUE KEY。

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

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

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

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

7. 如果 Child 表中已有数据，则 FK 列中的每个值都必须与 Parent 表 PK 列中的值匹配。用这样的查询来检查这个:

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

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

8. Parent 表和 Child 表都不能是 TEMPORARY 表。

9. Parent 表和 Child 表都不能是 PARTITIONED 表。

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

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

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

希望这会有所帮助。