MySQL:不能创建表(errno: 150)

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)

31 个解决方案

#1

From the MySQL - FOREIGN KEY Constraints Documentation:

从MySQL -外键约束文档:

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返回错误1005，并在错误消息中引用错误150，这意味着一个外键约束没有正确地形成。类似地，如果一个ALTER TABLE因Error 150而失败，这意味着对更改的表将不正确地生成一个外键定义。

#2

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意味着你的外键有问题。也许在国外的桌子上的钥匙不是完全一样的?

#3

You can get the actual error message by running SHOW ENGINE INNODB STATUS; and then looking for LATEST FOREIGN KEY ERROR in the output.

您可以通过运行SHOW ENGINE INNODB状态获得实际的错误消息;然后在输出中查找最新的外键错误。

Source: answer from another user in a similar question

来源:从另一个用户回答类似的问题。

#4

Data types must match exactly. If you are dealing with varchar types, the tables must use the same collation.

数据类型必须完全匹配。如果您正在处理varchar类型，那么表必须使用相同的排序规则。

#5

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.

因为很自然地，恢复将在外部表存在之前创建一些约束。

#6

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。两者都需要相同。

#7

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表示外键约束失败。您可能在表的外键依赖于(表关键字)之前创建此表。首先创建这个表，它应该可以正常工作。

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.

如果没有，删除外键语句并在创建表之后添加它——您将获得关于特定约束失败的更有意义的错误消息。

#8

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状态，就有一个名为“最新的外键错误”的部分。它会给你一个非常有用的错误信息，通常会告诉你什么是问题。您需要超级特权来运行它，所以如果您没有这样的权限，您只需要测试以下场景。

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)在非空列上使用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)

父列实际上并不存在于父表中。检查拼写(或者在列的开头或结尾的空格)

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)在某些系统上，如果您的报表和表名存在差异。如果你从一个服务器转到另一个服务器，它有不同的案例处理规则。

#9

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 fucky statement:

有时MySQL超级愚蠢——我能理解外键的原因。但是在我的例子中，我已经删除了整个数据库，我仍然会得到错误…为什么?我的意思是，再也没有数据库了……我所使用的sql用户无法访问服务器上的任何其他db。我的意思是，对于当前用户，服务器是“空的”，我仍然会得到这个错误?抱歉，我想MySQL在骗我…但我可以处理它:)只需在你的fucky语句周围添加这两行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…如果你真的有一个外键问题，它会显示在你的线上，你将再次启用检查-这将会失败。但是我的服务器很安静:)

#10

I had same error, then i have created referenced table first and then referred table

我有相同的错误，然后我先创建了引用表，然后再引用表。

for example if you have employee and department tables your assigning foreign constraint on dept_no in employee table then make sure that the department table is created and have assigned primary key constraints to dept_no.

例如，如果您有employee和department表，那么在employee表中指定dept_no的外部约束，然后确保创建department表，并将主键约束分配给dept_no。

this worked for me...

这工作对我来说…

#11

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 - error 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.
• 引用列再次必须具有与引用列相同的数据类型。

Satisfy these requirements and all will be well.

满足这些要求，一切都会好的。

#12

Change the engines of your tables, only innoDB supports foreign keys

更改表的引擎，只有innoDB支持外键。

#13

If the PK table is created in one CHARSET and then you create FK table in another CHARSET..then also you might get this error...I too got this error but after changing the charset to PK charset then it got executed without errors

如果在一个CHARSET中创建了PK表，然后在另一个字符集中创建FK表。然后你可能会得到这个错误…我也得到了这个错误，但是在将charset更改为PK字符集之后，它就被执行了，没有出现错误。

create table users
(
------------
-------------
)DEFAULT CHARSET=latin1;


create table Emp
(
---------
---------
---------
FOREIGN KEY (userid) REFERENCES users(id) on update cascade on delete cascade)ENGINE=InnoDB, DEFAULT CHARSET=latin1;

#14

This error can occur if two tables have a reference, for example, one table is Student and another table is Education, and we want the Education table to have a foreign key reference of Student table. In this instance the column data type for both tables should be same, otherwise it will generate an error.

如果两个表有一个引用，例如，一个表是Student，另一个表是Education，那么这个错误就会发生，我们希望教育表有一个学生表的外键引用。在此实例中，两个表的列数据类型应该相同，否则将生成错误。

#15

I experienced this error when have ported Windows application to Linux. In Windows, database table names are case-insensitive, and in Linux they are case-sensitive, probably because of file system difference. So, on Windows table Table1 is the same as table1, and in REFERENCES both table1 and Table1 works. On Linux, when application used table1 instead of Table1 when it created database structure I saw error #150; when I made correct character case in Table1 references, it started to work on Linux too. So, if nothing else helps, make you sure that in REFERENCES you use correct character case in table name when you on Linux.

在将Windows应用程序移植到Linux时，我经历了这个错误。在Windows中，数据库表名是不区分大小写的，在Linux中它们是区分大小写的，这可能是因为文件系统的差异。因此，在Windows表表1中，Table1和Table1都是一样的。在Linux上，当应用程序使用table1而不是table1时，当它创建数据库结构时，我看到了错误#150;当我在Table1引用中做了正确的字符实例时，它也开始在Linux上工作。因此，如果没有其他的帮助，请确保在引用Linux时，在引用中使用正确的字符用例。

#16

In most of the cases the problem is because of the ENGINE dIfference .If the parent is created by InnoDB then the referenced tables supposed to be created by MyISAM & vice versa

在大多数情况下，问题是由于引擎的不同。如果父是由InnoDB创建的，那么被引用的表应该由MyISAM创建，反之亦然。

#17

Please make sure both your primary key column and referenced column have the same data types and attributes (unsigned, binary, unsigned zerofill etc).

请确保您的主键列和引用列都具有相同的数据类型和属性(无符号、二进制、无符号零填充等)。

#18

A real edge case is where you have used an MySQL tool, (Sequel Pro in my case) to rename a database. Then created a database with the same name.

一个真正的边界情况是您使用一个MySQL工具(在我的例子中是Sequel Pro)来重命名一个数据库。然后创建具有相同名称的数据库。

This kept foreign key constraints to the same database name, so the renamed database (e.g. my_db_renamed) had foreign key constraints in the newly created database (my_db)

这将外键约束保留到相同的数据库名称，因此重命名的数据库(例如my_db_)在新创建的数据库中具有外键约束(my_db)

Not sure if this is a bug in Sequel Pro, or if some use case requires this behaviour, but it cost me best part of a morning :/

不确定这是不是Sequel Pro里的一个bug，或者某个用例需要这种行为，但是它花费了我一个上午的大部分时间:/。

#19

I had the same error. In my case the reason for the error was that I had a ON DELETE SET NULL statement in the constraint while the field on which I put the constraint in its definition had a NOT NULL statement. Allowing NULL in the field solved the problem.

我犯了同样的错误。在我的例子中，错误的原因是我在约束中有一个ON DELETE SET NULL语句，而我在它的定义中放入约束的字段有一个NOT NULL语句。在字段中允许NULL解决了这个问题。

#20

In my case. I had problems with engine and charset because my Hosting server change settings and my new tables was MyISAM but my old tables are InnoDB. Just i changed.

在我的例子中。我有引擎和字符集的问题，因为我的主机服务器改变了设置，我的新表是MyISAM，但是我的旧表是InnoDB。我改变了。

#21

I had a similar problem but mine was because i was adding a new field to an existing table that had data , and the new field was referencing another field from the parent table and also had the Defination of NOT NULL and without any DEFAULT VALUES. - I found out the reason things were not working was because

我有一个类似的问题，但是我的问题是，因为我在现有的表中添加了一个新的字段，它有数据，而新字段引用了父表中的另一个字段，并且定义了NOT NULL和没有任何默认值。-我发现事情不正常的原因是。

1. My new field needed to autofill the blank fields with a value from the parent table on each record, before the constraint could be applied. Every time the constraint is applied it needs to leave the Integrity of the table data intact. Implementing the Constraint (Foreign Key) yet there were some database records that did not have the values from the parent table would mean the data is corrupt so MySQL would NEVER ENFORCE YOUR CONSTRAINT
2. 在应用约束之前，我的新字段需要在每个记录上自动填充父表的值。每次应用约束时，都需要保持表数据的完整性。实现约束(外键)还有一些数据库记录没有来自父表的值，这意味着数据是坏的，所以MySQL不会强制执行您的约束。

It is important to remember that under normal circumstances if you planned your database well ahead of time, and implemented constraints before data insertion this particular scenario would be avoided

重要的是要记住，在正常情况下，如果您提前计划好了数据库，并且在数据插入之前实现了约束，那么这个特定的场景将会被避免。

The easier Approach to avoid this gotcha is to

避免这种问题的更简单的方法是。

• Save your database tables data
• 保存数据库表数据。
• Truncate the table data (and table artifacts i.e indexes etc)
• 截断表数据(和表工件i)。e指标等)
• Apply the Constraints
• 应用约束
• Import Your Data
• 导入数据

I Hope this helps someone

我希望这能帮助别人。

#22

Perhaps this will help? The definition of the primary key column should be exactly the same as the foreign key column.

也许这将帮助?主键列的定义应该与外键列完全相同。

#23

Make sure that the all tables can support foreign key - InnoDB engine

确保所有表都支持外键- InnoDB引擎。

#24

The column of PARENT table to which you are referring to from child table has to be unique. If it is not, cause an error no 150.

从子表中引用的父表的列必须是惟一的。如果不是，就会导致错误150。

#25

I had a similar problem when dumping a Django mysql database with a single table. I was able to fix the problem by dumping the database to a text file, moving the table in question to the end of the file using emacs and importing the modified sql dump file into the new instance.

我在用一个表转储Django mysql数据库时遇到了类似的问题。我可以通过将数据库转储到文本文件来解决这个问题，使用emacs将表移动到文件的末尾，并将修改后的sql转储文件导入到新实例中。

HTH Uwe

HTH乌维

#26

I faced this kind of issue while creating DB from the textfile.

在从textfile创建DB时，我遇到了这样的问题。

mysql -uroot -padmin < E:\important\sampdb\createdb.sql
mysql -uroot -padmin sampdb < E:\important\sampdb\create_student.sql
mysql -uroot -padmin sampdb < E:\important\sampdb\create_absence.sql

mysql -uroot -padmin sampdb < E:\important\sampdb\insert_student.sql
mysql -uroot -padmin sampdb < E:\important\sampdb\insert_absence.sql

mysql -uroot -padmin sampdb < E:\important\sampdb\load_student.sql
mysql -uroot -padmin sampdb < E:\important\sampdb\load_absence.sql 

I just wrote the above lines in Create.batand run the bat file.

我只是写了上面的线。batand运行bat文件。

My mistake is in the sequence order of execution in my sql files. I tried to create table with primary key and also foreign key. While its running it will search for the reference table but tables are not there. So it will return those kind of error.

我的错误是在sql文件中执行顺序。我尝试用主键和外键创建表。当它运行时，它将搜索引用表，但是表不在那里。所以它会返回那些错误。

If you creating tables with foreign key then check the reference tables were present or not. And also check the name of the reference tables and fields.

如果使用外键创建表，则检查引用表是否存在。还要检查引用表和字段的名称。

#27

I've corrected the problem by making the variable accept null

我已经通过让变量接受null来纠正这个问题。

ALTER TABLE `ajout_norme` 
CHANGE `type_norme_code` `type_norme_code` VARCHAR( 2 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL

#28

I got the same problem when executing a series of MySQL commands. Mine occurs during creating a table when referencing a foreign key to other table which was not created yet. It's the sequence of table existence before referencing.

在执行一系列MySQL命令时，我遇到了同样的问题。在创建表时，当引用尚未创建的其他表的外键时，就会发生。它是在引用之前的表存在序列。

The solution: Create the parent tables first before creating a child table which has a foreign key.

解决方案:首先创建父表，然后创建具有外键的子表。

#29

Create the table without foreign key, then set the foreign key separately.

创建没有外键的表，然后分别设置外键。

#30

usually, the mismatch between foreign key & primary key causes the error:150.

通常，外键和主键之间的不匹配导致错误:150。

The foreign key must have the same datatype as the primary key. Also, if the primary key is unsigned then the foreign key must also be unsigned.

外键必须具有与主键相同的数据类型。另外，如果主键没有签名，那么外键也必须是无符号的。