MySQL-Fehler 150: Fehlerbehebung bei Fremdschlüsseleinschränkungen beim Erstellen einer Tabelle

Question

Ich versuche, eine Tabelle in MySQL mit zwei Fremdschlüsseln zu erstellen, die auf Primärschlüssel in zwei anderen Tabellen verweisen, erhalte jedoch eine errno: 150-Fehlermeldung und die Tabelle wird nicht erstellt.

Hier ist die SQL für alle 3 Tabellen:

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 的回答。