错误代码:1005。不能创建表'…' (errno: 150)

I searched for a solution to this problem on internet and checked the SO questions but no solution worked for my case.

我在网上找了一个解决这个问题的办法，检查了SO的问题，但是没有一个解决方案适合我的情况。

I want to create a foreign key from table sira_no to metal_kod.

我想从表sira_no到metal_kod创建一个外键。

ALTER TABLE sira_no 
    ADD CONSTRAINT METAL_KODU FOREIGN KEY(METAL_KODU) 
    REFERENCES metal_kod(METAL_KODU) 
    ON DELETE SET NULL 
    ON UPDATE SET NULL ;

This script returns:

该脚本返回:

Error Code: 1005. Can't create table 'ebs.#sql-f48_1a3' (errno: 150)

I tried adding index to the referenced table:

我尝试将索引添加到引用的表中:

CREATE INDEX METAL_KODU_INDEX ON metal_kod (METAL_KODU);

I checked METAL_KODU on both tables (charset and collation). But couldn't find a solution to this problem. Does anyone have any idea? Thanks in advance.

我检查了两个表上的METAL_KODU (charset和collation)。但是还没有找到解决这个问题的办法。有人知道吗?提前谢谢。

EDIT: Here is the metal_kod table:

编辑:这是metal_kod表:

METAL_KODU  varchar(4)  NO  PRI     
DURUM   bit(1)  NO          
METAL_ISMI  varchar(30) NO          
AYAR_YOGUNLUK   smallint(6) YES     100

11 个解决方案

#1

245

Error Code: 1005 -- there is a wrong primary key reference in your code

错误代码:1005——您的代码中有错误的主键引用

usually it's due to a reference FK field not exist. might be you have typo mistake,or check case it should be same, or there's a field-type mismatch. FK-linked fields must match definitions exactly.

通常是因为引用FK字段不存在。可能是打印错误，或者检查情况应该是相同的，或者是字段类型不匹配。fk链接字段必须完全匹配定义。

Some Known causes may be :

一些已知的原因可能是:

The two key fields type and/or size doesn’t match exactly. For example, if one is INT(10) the key field needs to be INT(10) as well and not INT(11) or TINYINT. You may want to confirm the field size using SHOW CREATE TABLE because Query Browser will sometimes visually show just INTEGER for both INT(10) and INT(11). You should also check that one is not SIGNED and the other is UNSIGNED. They both need to be exactly the same.
两个关键字段类型和/或大小并不完全匹配。例如，如果一个是INT(10)，那么键字段也必须是INT(10)，而不是INT(11)或TINYINT。您可能希望使用SHOW CREATE TABLE来确认字段的大小，因为查询浏览器有时会在视觉上显示INT(10)和INT(11)的整型。您还应该检查一个没有签名，另一个没有签名。它们都需要完全相同。
One of the key field that you are trying to reference does not have an index and/or is not a primary key. If one of the fields in the relationship is not a primary key, you must create an index for that field.
您试图引用的关键字段之一没有索引，而且/或不是主键。如果关系中的一个字段不是主键，则必须为该字段创建索引。
The foreign key name is a duplicate of an already existing key. Check that the name of your foreign key is unique within your database. Just add a few random characters to the end of your key name to test for this.
外键名是已经存在的键的副本。检查外键的名称是否在数据库中是唯一的。只需在密钥名的末尾添加一些随机字符，以进行测试。
One or both of your tables is a MyISAM table. In order to use foreign keys, the tables must both be InnoDB. (Actually, if both tables are MyISAM then you won’t get an error message - it just won’t create the key.) In Query Browser, you can specify the table type.
您的一个或两个表是MyISAM表。为了使用外键，表必须都是InnoDB。(实际上，如果两个表都是MyISAM，那么您就不会得到错误消息——它不会创建密钥。)在Query Browser中，可以指定表类型。
You have specified a cascade ON DELETE SET NULL, but the relevant key field is set to NOT NULL. You can fix this by either changing your cascade or setting the field to allow NULL values.
您已经在DELETE SET NULL上指定了一个级联，但是相关的键字段被设置为NOT NULL。您可以通过更改级联或设置字段以允许空值来解决这个问题。
Make sure that the Charset and Collate options are the same both at the table level as well as individual field level for the key columns.
确保Charset和Collate选项在表级别和键列的单独字段级别上是相同的。
You have a default value (ie default=0) on your foreign key column
在您的外键列上有一个默认值(默认值为0)。
One of the fields in the relationship is part of a combination (composite) key and does not have it’s own individual index. Even though the field has an index as part of the composite key, you must create a separate index for only that key field in order to use it in a constraint.
关系中的一个字段是组合(复合)键的一部分，它没有自己的单独索引。即使该字段有一个索引作为复合键的一部分，您也必须为该键字段创建一个单独的索引，以便在约束中使用它。
You have a syntax error in your ALTER statement or you have mistyped one of the field names in the relationship
在ALTER语句中出现语法错误，或者在关系中输入了一个字段名
The name of your foreign key exceeds the max length of 64 chars.
您的外键的名称超过了64个字符的最大长度。

for more details refer : MySQL Error Number 1005 Can’t create table

更多细节请参阅:MySQL错误编号1005无法创建表

#2

This could also happen when exporting your database from one server to another and the tables are listed in alphabetical order by default.
So, your first table could have a foreign key of another table that is yet to be created. In such cases, disable foreign_key_checks and create the database.

当将数据库从一台服务器导出到另一台服务器并默认按字母顺序列出表时，也可能发生这种情况。因此，您的第一个表可以有另一个尚未创建的表的外键。在这种情况下，禁用foreign_key_check并创建数据库。

Just add the following to your script:

只要在你的脚本中添加以下内容:

SET FOREIGN_KEY_CHECKS=0;

and it shall work.

和它的工作。

#3

Very often it happens, when the foreign key and the reference key don't have same type or same length

当外键和引用键没有相同的类型或长度时，通常会发生这种情况

#4

I know this is little late answer but I thought this could be helpful to someone.

我知道这不是一个迟来的回答，但我认为这可能对某些人有帮助。

Sometimes it is due to the master table is dropped (maybe by disabling foreign_key_checks) but the foreign key CONSTRAINT still exist in other tables. In My case I had dropped the table and tried to recreate it but it was throwing the same error for me.

有时是由于主表被删除(可能通过禁用foreign_key_check)，但是外键约束仍然存在于其他表中。在我的例子中，我把桌子弄丢了，并试图重新创建它，但它给我带来了同样的错误。

So try dropping all the foreign key CONSTRAINT from all the tables if there are any and then update or create the table.

因此，尝试删除所有表中的外键约束(如果有的话)，然后更新或创建表。

#5

Error Code: 1005

错误代码:1005

Hello, I am putting across this answer so that anybody facing similar issue like mine can benefit for this response. Trust me this can be overlooked) (this may have been already answered and if so please excuse me)

大家好，我将给出这个答案，以便像我这样遇到类似问题的人能够从中受益。相信我，这是可以忽略的)(这可能已经被回答过了，如果是，请原谅我)

I had similar issue, so here are few things that I did try (not in any order except for the solution :) )

我也遇到过类似的问题，所以这里有一些我尝试过的事情(除了解决方案:)

Changed the foreign key names(didn't work)
更改外键名(无效)
Reduced the foreign key length
减少外键长度
Verified the datatypes (darn nothing wrong)
验证数据类型(该死，没什么问题)
Check indexes
检查索引
Check the collations (everything fine, darn again)
检查排序(一切正常，再来一次)
Truncated the table, of no good use
把桌子截短，没有用。
Dropped the table and re-created
删除表并重新创建
Tried to see if any circular reference is being created --- all fine
试着看看是否有循环引用被创建——都很好

9. Finally, I saw that i had two editors open. One that in PHPStorm (jetbrains) and the other MySQL workbench. It seems that the PHPStorm / SQL workbench creates some kind of edit lock. I closed PHPStorm just to check if locking was the case (it could have been the other way around). This solved my problem. Hope this helps someone having similar issue.

9。最后，我看到有两个编辑器打开。一个在PHPStorm (jetbrains)和另一个MySQL工作台。PHPStorm / SQL工作台似乎创建了某种编辑锁。我关闭了PHPStorm以检查是否锁定了(它可能是另一种情况)。这解决了我的问题。希望这对有类似问题的人有所帮助。

#6

I had a similar error. The problem had to do with the child and parent table not having the same charset and collation. This can be fixed by appending ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;

我也有类似的错误。问题与子表和父表没有相同的字符集和排序有关。可以通过追加ENGINE = InnoDB默认字符集= utf8来解决这个问题;

CREATE TABLE IF NOT EXISTS `country` (`id` INT(11) NOT NULL AUTO_INCREMENT,...) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8;

... on the SQL statement means that there is some missing code.

…在SQL语句上意味着缺少一些代码。

#7

It happened in my case because the name of the table being referenced in the constraint declaration isn't correct (i forgot the upper case in the table name)

在我的例子中之所以发生这种情况，是因为约束声明中引用的表的名称不正确(我忘记了表名中的大小写)

ALTER TABLE `Window` ADD CONSTRAINT `Windows_ibfk_1` FOREIGN KEY (`WallId`) REFERENCES `Wall` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

I hope that helps.

我希望有帮助。

#8

The foreign key has to have the exact same type as the primary key that it references. For the example has the type “INT UNSIGNED NOT NULL” the foreing key also have to “INT UNSIGNED NOT NULL”

外键必须具有与其引用的主键相同的类型。例如，类型为" INT UNSIGNED NOT NULL "前面的关键字也必须是" INT UNSIGNED NOT NULL "

CREATE TABLE employees(
id_empl INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id)
);
CREATE TABLE offices(
id_office INT UNSIGNED NOT NULL AUTO_INCREMENT,
id_empl INT UNSIGNED NOT NULL,
PRIMARY KEY(id),
CONSTRAINT `constraint1` FOREIGN KEY (`id_empl`) REFERENCES `employees` (`id_empl`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='my offices';

#9

MyISAM has been just mentioned. Simply try adding ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; at the end of a statement, assuming that your other tables were created with MyISAM.

MyISAM刚刚提到过。简单地尝试添加引擎=MyISAM默认字符集=latin1 AUTO_INCREMENT=2;在语句末尾，假设您的其他表是用MyISAM创建的。

CREATE TABLE IF NOT EXISTS `tablename` (
  `key` bigint(20) NOT NULL AUTO_INCREMENT,
  FOREIGN KEY `key` (`key`) REFERENCES `othertable`(`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

#10

In my case, it happened when one table is InnoB and other is MyISAM. Changing engine of one table, through MySQL Workbench, solves for me.

在我的案例中，当一个表是InnoB，另一个是MyISAM时，它就发生了。通过MySQL工作台更改一个表的引擎，解决了我的问题。

#11

I had the very same error message. Finally I figured out I misspelled the name of the table in the command:

我得到了同样的错误信息。最后我发现我在命令中把表格的名字拼错了:

ALTER TABLE `users` ADD FOREIGN KEY (country_id) REFERENCES country (id);

versus

与

ALTER TABLE `users` ADD FOREIGN KEY (country_id) REFERENCES countries (id);

I wonder why on earth mysql cannot tell such a table does not exist...

我想知道为什么mysql不能告诉这样的表不存在……

#1

245