MySQL 创建带有外键的表给出 errno: 150

2021-11-20 00:00:00 foreign-keys mysql mysql-error-150

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

I am trying to create a table in MySQL with two foreign keys, which reference the primary keys in 2 other tables, but I am getting an errno: 150 error and it will not create the table.

这是所有 3 个表的 SQL:

Here is the SQL for all 3 tables:

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;

任何帮助将不胜感激.

推荐答案

我在使用 ALTER TABLE ADD FOREIGN KEY 时遇到了同样的问题.

I had the same problem with ALTER TABLE ADD FOREIGN KEY.

一个小时后，我发现必须满足这些条件才不会出现错误 150:

After an hour, I found that these conditions must be satisfied to not get error 150:

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

  1. The Parent table must exist before you define a foreign key to reference it. You must define the tables in the right order: Parent table first, then the Child table. If both tables references each other, you must create one table without FK constraints, then create the second table, then add the FK constraint to the first table with ALTER TABLE.

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

The two tables must both support foreign key constraints, i.e. ENGINE=InnoDB. Other storage engines silently ignore foreign key definitions, so they return no error or warning, but the FK constraint is not saved.

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

The referenced columns in the Parent table must be the left-most columns of a key. Best if the key in the Parent is PRIMARY KEY or UNIQUE KEY.

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

The FK definition must reference the PK column(s) in the same order as the PK definition. For example, if the FK REFERENCES Parent(a,b,c) then the Parent's PK must not be defined on columns in order (a,c,b).

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

The PK column(s) in the Parent table must be the same data type as the FK column(s) in the Child table. For example, if a PK column in the Parent table is UNSIGNED, be sure to define UNSIGNED for the corresponding column in the Child table field.

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

Exception: length of strings may be different. For example, VARCHAR(10) can reference VARCHAR(20) or vice versa.

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

Any string-type FK column(s) must have the same character set and collation as the corresponding PK column(s).

如果子表中已有数据，则 FK 列中的每个值都必须与父表 PK 列中的值匹配.使用以下查询进行检查:

If there is data already in the Child table, every value in the FK column(s) must match a value in the Parent table PK column(s). Check this with a query like:

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

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

This must return zero (0) unmatched values. Obviously, this query is an generic example; you must substitute your table names and column names.

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

Neither the Parent table nor the Child table can be a TEMPORARY table.

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

Neither the Parent table nor the Child table can be a PARTITIONED table.

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

If you declare a FK with the ON DELETE SET NULL option, then the FK column(s) must be nullable.

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

If you declare a constraint name for a foreign key, the constraint name must be unique in the whole schema, not only in the table in which the constraint is defined. Two tables may not have their own constraint with the same name.

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

If there are any other FK's in other tables pointing at the same field you are attempting to create the new FK for, and they are malformed (i.e. different collation), they will need to be made consistent first. This may be a result of past changes where SET FOREIGN_KEY_CHECKS = 0; was utilized with an inconsistent relationship defined by mistake. See @andrewdotn's answer below for instructions on how to identify these problem FK's.

希望这会有所帮助.

相关文章