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

MySQL Creating tables with Foreign Keys giving errno: 150

提问by marv-el

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.

我试图在 MySQL 中创建一个带有两个外键的表，它引用了另外两个表中的主键，但是我收到了一个 errno: 150 错误，它不会创建表。

Here is the SQL for all 3 tables:

这是所有 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;

Any help would be greatly appreciated.

任何帮助将不胜感激。

回答by marv-el

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

我有同样的问题ALTER TABLE ADD FOREIGN KEY。

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

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

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.

2. 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.

3. 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 KEYor UNIQUE KEY.

4. 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).

5. 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 UNSIGNEDfor the corresponding column in the Child table field.

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

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

7. 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;
   

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

8. Neither the Parent table nor the Child table can be a TEMPORARYtable.

9. Neither the Parent table nor the Child table can be a PARTITIONEDtable.

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

11. 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.

12. 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.

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

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

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

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

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

   例外：字符串的长度可能不同。例如，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 的回答。

Hope this helps.

希望这可以帮助。

回答by andrewdotn

MySQL's generic “errno 150” message “means that a foreign key constraint was not correctly formed.” As you probably already know if you are reading this page, the generic “errno: 150” error message is really unhelpful. However:

MySQL 的通用“errno 150”消息“表示未正确形成外键约束。” 如果您正在阅读本页，您可能已经知道，一般的“errno: 150”错误消息确实没有帮助。然而：

You can get the actualerror message by running SHOW ENGINE INNODB STATUS;and then looking for LATEST FOREIGN KEY ERRORin the output.

您可以通过运行然后在输出中查找来获取实际的错误消息。SHOW ENGINE INNODB STATUS;LATEST FOREIGN KEY ERROR

For example, this attempt to create a foreign key constraint:

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

CREATE TABLE t1
(id INTEGER);

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

fails with the error Can't create table 'test.t2' (errno: 150). That doesn't tell anyone anything useful other than that it's a foreign key problem. But run SHOW ENGINE INNODB STATUS;and it will say:

因错误而失败Can't create table 'test.t2' (errno: 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.

It says that the problem is it can't find an index. SHOW INDEX FROM t1shows that there aren't any indexes at all for table t1. Fix that by, say, defining a primary key on t1, and the foreign key constraint will be created successfully.

它说问题是它找不到索引。SHOW INDEX FROM t1表明 table 根本没有任何索引t1。例如，通过在 上定义主键来修复该问题t1，外键约束将成功创建。

回答by Jon Winstanley

Make sure that the properties of the two fields you are trying to link with a constraint are exactly the same.

确保您尝试与约束链接的两个字段的属性完全相同。

Often, the 'unsigned' property on an ID column will catch you out.

通常，ID 列上的 'unsigned' 属性会引起您的注意。

ALTER TABLE `dbname`.`tablename` CHANGE `fieldname` `fieldname` int(10) UNSIGNED NULL;

回答by Brent Writes Code

What's the current state of your database when you run this script? Is it completely empty? Your SQL runs fine for me when creating a database from scratch, but errno 150 usually has to do with dropping & recreating tables that are part of a foreign key. I'm getting the feeling you're not working with a 100% fresh and new database.

运行此脚本时，数据库的当前状态是什么？它是完全空的吗？从头开始创建数据库时，您的 SQL 对我来说运行良好，但 errno 150 通常与删除和重新创建作为外键一部分的表有关。我感觉您没有使用 100% 全新的数据库。

If you're erroring out when "source"-ing your SQL file, you should be able to run the command "SHOW ENGINE INNODB STATUS" from the MySQL prompt immediately after the "source" command to see more detailed error info.

如果在“source”-ing SQL 文件时出错，您应该能够在“source”命令之后立即从 MySQL 提示符运行命令“SHOW ENGINE INNODB STATUS”以查看更详细的错误信息。

You may want to check out the manual entry too:

您可能还想查看手动输入：

If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to error 150 in the error message. If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed.

— MySQL 5.1 reference manual.

如果您重新创建一个被删除的表，它必须有一个符合引用它的外键约束的定义。如前所述，它必须具有正确的列名和类型，并且必须在所引用的键上具有索引。如果这些都不满足，MySQL 将返回错误号 1005 并在错误消息中引用错误 150。如果 MySQL 从 CREATE TABLE 语句报告错误号 1005，并且错误消息引用错误 150，则表创建失败，因为外键约束未正确形成。

— MySQL 5.1 参考手册。

回答by juacala

For people who are viewing this thread with the same problem:

对于正在查看此线程并遇到相同问题的人：

There are a lot of reasons for getting errors like this. For a fairly complete list of causes and solutions of foreign key errors in MySQL (including those discussed here), check out this link:

出现此类错误的原因有很多。有关 MySQL 中外键错误的原因和解决方案的相当完整列表（包括此处讨论的内容），请查看此链接：

MySQL Foreign Key Errors and Errno 150

MySQL 外键错误和 Errno 150

回答by Eric L.

For others that find this SO entry via Google: Be sure that you aren't trying to do a SET NULL action on a foreign key (to be) column defined as "NOT NULL." That caused great frustration until I remembered to do a CHECK ENGINE INNODB STATUS.

对于通过 Google 找到此 SO 条目的其他人：请确保您没有尝试对定义为“NOT NULL”的外键（待）列执行 SET NULL 操作。这引起了很大的挫败感，直到我记得做一个 CHECK ENGINE INNODB STATUS 。

回答by I159

Definitely it is not the case but I found this mistake pretty common and unobvious. The target of a FOREIGN KEYcould be not PRIMARY KEY. Te answer which become useful for me is:

事实并非如此，但我发现这个错误很常见且不明显。a 的目标FOREIGN KEY可能不是PRIMARY KEY。对我有用的答案是：

A FOREIGN KEY always must be pointed to a PRIMARY KEY true field of other table.

FOREIGN KEY 必须始终指向其他表的 PRIMARY KEY 真字段。

CREATE TABLE users(
   id INT AUTO_INCREMENT PRIMARY KEY,
   username VARCHAR(40));

CREATE TABLE userroles(
   id INT AUTO_INCREMENT PRIMARY KEY,
   user_id INT NOT NULL,
   FOREIGN KEY(user_id) REFERENCES users(id));

回答by MuchMore

As pointed by @andrewdotn the best way is to see the detailed error(SHOW ENGINE INNODB STATUS;) instead of just an error code.

正如@andrewdotn 所指出的，最好的方法是查看详细的 error( SHOW ENGINE INNODB STATUS;) 而不仅仅是错误代码。

One of the reasons could be that an index already exists with the same name, may be in another table. As a practice, I recommend prefixing table name before the index name to avoid such collisions. e.g. instead of idx_userIduse idx_userActionMapping_userId.

原因之一可能是同名索引已经存在，可能在另一个表中。作为一种实践，我建议在索引名称之前添加表名称前缀以避免此类冲突。例如，而不是idx_userId使用idx_userActionMapping_userId。

回答by Juljan

Please make sure at first that

请首先确保

1. you are using InnoDB tables.
2. field for FOREIGN KEY has the same type and length (!) as source field.

1. 您正在使用 InnoDB 表。
2. FOREIGN KEY 的字段与源字段具有相同的类型和长度 (!)。

I had the same trouble and I've fixed it. I had unsigned INT for one field and just integer for other field.

我遇到了同样的问题，我已经解决了。我有一个字段的无符号 INT 和其他字段的整数。

回答by sturrockad

Helpful tip, use SHOW WARNINGS;after trying your CREATEquery and you will receive the error as well as the more detailed warning:

有用的提示，SHOW WARNINGS;在尝试CREATE查询后使用，您将收到错误以及更详细的警告：

    ---------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                                 |
+---------+------+--------------------------------------------------------------------------                          --------------------------------------------------------------------------------------------                          ---------------+
| Warning |  150 | Create table 'fakeDatabase/exampleTable' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.
|
| Error   | 1005 | Can't create table 'exampleTable' (errno:150)                                                                                                                                                                           |
+---------+------+--------------------------------------------------------------------------                          --------------------------------------------------------------------------------------------                          ---------------+

So in this case, time to re-create my table!

所以在这种情况下，是时候重新创建我的表了！