MySQL：无法创建表（错误号：150）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4061293/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: Can't create table (errno: 150)
提问by gtilx
I am trying to import a .sql file and its failing on creating tables.
我正在尝试导入 .sql 文件，但在创建表时失败。
Here's the query that fails:
这是失败的查询：
CREATE TABLE `data` (
`id` int(10) unsigned NOT NULL,
`name` varchar(100) NOT NULL,
`value` varchar(15) NOT NULL,
UNIQUE KEY `id` (`id`,`name`),
CONSTRAINT `data_ibfk_1` FOREIGN KEY (`id`) REFERENCES `keywords` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
I exported the .sql from the the same database, I dropped all the tables and now im trying to import it, why is it failing?
我从同一个数据库中导出了 .sql，我删除了所有的表，现在我试图导入它，为什么它失败了？
MySQL: Can't create table './dbname/data.frm' (errno: 150)
MySQL：无法创建表 './dbname/data.frm' (errno: 150)
回答by OMG Ponies
From the MySQL - FOREIGN KEY Constraints Documentation:
来自MySQL - FOREIGN KEY Constraints Documentation：
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 correct column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns Error 1005 and refers to Error 150 in the error message, which means that a foreign key constraint was not correctly formed.Similarly, if an ALTER TABLE fails due to Error 150, this means that a foreign key definition would be incorrectly formed for the altered table.
如果您重新创建一个被删除的表，它必须有一个符合引用它的外键约束的定义。如前所述，它必须具有正确的列名和类型，并且必须在引用的键上具有索引。如果这些都不满足，MySQL 将返回 Error 1005 并在错误消息中引用 Error 150，这意味着未正确形成外键约束。类似地，如果 ALTER TABLE 由于错误 150 而失败，这意味着对于更改后的表，外键定义的格式不正确。
回答by Dan McGrath
Error 150 means you have a problem with your foreign key. Possibly the key on the foreign table isn't the exact same type?
错误 150 表示您的外键有问题。可能外部表上的键不是完全相同的类型？
回答by Denilson Sá Maia
You can get the actual error 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在输出中查找来获取实际的错误消息。
回答by Esben Skov Pedersen
Data types must match exactly. If you are dealing with varchar types, the tables must use the same collation.
数据类型必须完全匹配。如果您正在处理 varchar 类型，则表必须使用相同的排序规则。
回答by colin
I think all these answers while correct are misleading to the question.
我认为所有这些答案虽然正确，但都误导了这个问题。
The actual answer is this before you start a restore, if you're restoring a dump file with foreign keys:
如果您要使用外键还原转储文件，则在开始还原之前，实际答案是这样的：
SET FOREIGN_KEY_CHECKS=0;
because naturally the restore will be creating some constraints before the foreign table even exists.
因为在外部表甚至存在之前，还原自然会创建一些约束。
回答by pi.
In some cases, you may encounter this error message if there are different engines between the relating tables. For example, a table may be using InnoDB while the other uses MyISAM. Both need to be same
在某些情况下，如果相关表之间存在不同的引擎，您可能会遇到此错误消息。例如，一个表可能使用 InnoDB 而另一个使用 MyISAM。两者必须相同
回答by Eran Galperin
Error no. 150 means a foreign key constraint failure. You are probably creating this table before the table the foreign key depends on (table keywords). Create that table first and it should work fine.
错误编号 150 表示外键约束失败。您可能在外键所依赖的表 (table keywords)之前创建此表。首先创建该表，它应该可以正常工作。
If it doesn't, remove the foreign key statement and add it after the table is created - you will get a more meaningful error message about the specific constraint failure.
如果没有，请删除外键语句并在创建表后添加它 - 您将获得有关特定约束失败的更有意义的错误消息。
回答by juacala
There are quite a few things that can cause errno 150, so for people searching this topic, here is what I think is a close to exhaustive list (source Causes of Errno 150):
有很多事情会导致 errno 150，所以对于搜索这个主题的人，我认为这是一个接近详尽的列表（来源原因 Errno 150）：
For errno 150 or errno 121, simply typing in SHOW ENGINE INNODB STATUS, there is a section called "LATEST FOREIGN KEY ERROR". Under that it will give you a very helpful error message, which typically will tell you right away what is the matter. You need SUPER privileges to run it, so if you don't have that, you'll just have to test out the following scenarios.
对于 errno 150 或 errno 121，只需在 SHOW ENGINE INNODB STATUS 中输入，就会有一个名为“LATEST FOREIGN KEY ERROR”的部分。在此之下，它将为您提供非常有用的错误消息，通常会立即告诉您问题所在。你需要超级权限才能运行它，所以如果你没有，你只需要测试以下场景。
1) Data Types Don't Match: The types of the columns have to be the same
1）数据类型不匹配：列的类型必须相同
2) Parent Columns Not Indexed (Or Indexed in Wrong Order)
2) 父列未索引（或索引顺序错误）
3) Column Collations Don't Match
3) 列排序规则不匹配
4) Using SET NULL on a NOT NULL Column
4) 在 NOT NULL 列上使用 SET NULL
5) Table Collations Don't Match: even if the column collations match, on some MySQL versions this can be a problem.
5) 表排序规则不匹配：即使列排序规则匹配，在某些 MySQL 版本上这也可能是一个问题。
6) Parent Column Doesn't Actually Exist In Parent Table. Check spelling (and perhaps a space at the beginning or end of column)
6) 父列实际上并不存在于父表中。检查拼写（可能还有列开头或结尾的空格）
7) One of the indexes on one of the columns is incomplete, or the column is too long for a complete index. Note that MySQL (unless you tweak it) has a maximum single column key length of 767 bytes (this corresponds to a varchar(255) UTF column)
7) 其中一列的索引之一不完整，或者该列太长，无法形成完整的索引。请注意，MySQL（除非您对其进行调整）的最大单列键长度为 767 字节（这对应于 varchar(255) UTF 列）
In case you get an errno 121, here are a couple of causes:
如果您收到 errno 121，以下是几个原因：
1) The constraint name you chose is already taken
1）您选择的约束名称已被采用
2) On some systems if there is a case difference in your statement and table names. This can bite you if you go from one server to another that have different case handling rules.
2) 在某些系统上，如果您的语句和表名存在大小写差异。如果您从一台服务器转到具有不同案例处理规则的另一台服务器，这可能会咬您。
回答by jebbie
Sometimes MySQL is just super stupid - i can understand the reason cause of foreign-keys.. but in my case, i have just dropped the whole database, and i still get the error... why? i mean, there is no database anymore... and the sql-user i'm using has no access to any other db's on the server... i mean, the server is "empty" for the current user and i still get this error? Sorry but i guess MySQL is lying to me... but i can deal with it :) Just add these two lines of SQL around your fwory statement:
有时 MySQL 只是超级愚蠢 - 我可以理解外键的原因..但在我的情况下，我刚刚删除了整个数据库，但我仍然收到错误......为什么？我的意思是，不再有数据库了……我正在使用的 sql-user 无法访问服务器上的任何其他数据库……我的意思是，当前用户的服务器是“空的”，我仍然得到这个错误？对不起，但我猜 MySQL 是在骗我……但我可以处理它:) 只需在你的该死的语句周围添加这两行 SQL：
SET FOREIGN_KEY_CHECKS = 0;
# some code that gives you errno: 150
SET FOREIGN_KEY_CHECKS = 1;
Now the sql should be executed... If you really have a foreign-key problem, it would show up to you by the line where you will enable the checks again - this will fail then.. but my server is just quiet :)
现在应该执行 sql ......如果你真的有外键问题，它会通过你将再次启用检查的行显示给你 - 这将失败......但我的服务器只是安静:)
回答by Davies Malesi
After cruising through the answers above, and experimenting a bit, this is an effective way to solve Foreign Key errors in MySQL (1005 - error 150).
在浏览上述答案并进行一些试验后，这是解决 MySQL 中的外键错误（1005 - 错误 150）的有效方法。
For the foreign key to be properly created, all MySQL asks for is:
为了正确创建外键，所有 MySQL 要求的是：
- All referenced keys MUST have either PRIMARY or UNIQUE index.
- Referencing Column again MUST have identical data type to the Referenced column.
- 所有引用的键必须具有 PRIMARY 或 UNIQUE 索引。
- 再次引用列必须与被引用列具有相同的数据类型。
Satisfy these requirements and all will be well.
满足这些要求，一切都会好起来的。
