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

Question

我试图在MySQL中创建一个有两个外键的表，这两个外键引用了其他两个表中的主键，但我得到一个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

MySQL的泛型“errno:150”消息“意味着外键约束没有正确形成”，如果您正在阅读本页，您可能已经知道，泛型“errno:150”错误消息实际上是没有帮助的。然而：

通过运行show ENGINE INNODB status；，然后在输出中查找latest FOREIGN KEY error，可以获得实际的错误消息。

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

sql prettyprint-override">CREATE TABLE t1
(id INTEGER);

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

失败，出现错误无法创建表“test.t2”（错误号: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 2

我在alter TABLE ADD FOREIGN key中遇到了同样的问题。

一个小时后，我发现必须满足这些条件才能不得到错误150：

>

父表必须存在，然后才能定义外键来引用它。必须按照正确的顺序定义表：首先是父表，然后是子表。如果两个表都相互引用，则必须创建一个没有FK约束的表，然后创建第二个表，然后使用alter table将FK约束添加到第一个表。

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

父表中的引用列必须是键的最左边的列。最好是父项中的键是主键或唯一键。

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

父表中的PK列必须与子表中的FK列具有相同的数据类型。例如，如果父表中的PK列为unsigned，请确保为子表字段中的相应列定义unsigned。

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

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

如果子表中已有数据，则FK列中的每个值必须与父表PK列中的值匹配。使用以下查询检查此内容：

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

这必须返回零（0）个不匹配值。显然,此查询是一个通用示例；必须替换表名和列名。

父表和子表都不能是临时表。

父表和子表都不能是分区表。

如果使用on DELETE SET null选项声明FK，则FK列必须是可为null的。

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

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

希望这有帮助。