MySQL 错误 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。

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 IS NULL;

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

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

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

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

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

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