MySQL 错误代码：1005。无法创建表“...”（错误号：150）

Error Code: 1005. Can't create table '...' (errno: 150)

提问by lamostreta

I searched for a solution to this problem on the Internet and checked the Stack Overflow questions, but none of the solutions worked for my case.

我在互联网上搜索了这个问题的解决方案并检查了 Stack Overflow 问题，但没有一个解决方案适用于我的情况。

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 an 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 I couldn't find a solution to this problem. How can I fix this problem?

我在两个表（字符集和排序规则）上都检查了 METAL_KODU，但找不到解决此问题的方法。我该如何解决这个问题？

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

回答by

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

错误代码：1005 -- 您的代码中有错误的主键引用

Usually it's due to a referenced foreign key field that does not exist. It might be you have a typo mistake, or check case it should be same, or there's a field-type mismatch. Foreign key-linked fields must match definitions exactly.

通常是由于引用的外键字段不存在。可能是您有拼写错误，或者检查大小写应该相同，或者字段类型不匹配。外键链接字段必须完全匹配定义。

Some known causes may be:

一些已知的原因可能是：

1. 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 SHOWCREATETABLEbecause Query Browser will sometimes visually show just INTEGERfor both INT(10)and INT(11). You should also check that one is not SIGNEDand the other is UNSIGNED. They both need to be exactly the same.
2. 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.
3. 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.
4. One or both of your tables is a MyISAMtable. In order to use foreign keys, the tables must both be InnoDB. (Actually, if both tables are MyISAMthen you won't get an error message - it just won't create the key.) In Query Browser, you can specify the table type.
5. You have specified a cascade ONDELETESETNULL, but the relevant key field is set to NOTNULL. You can fix this by either changing your cascade or setting the field to allow NULLvalues.
6. 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.
7. You have a default value (that is, default=0) on your foreign key column
8. One of the fields in the relationship is part of a combination (composite) key and does not have its 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.
9. You have a syntax error in your ALTERstatement or you have mistyped one of the field names in the relationship
10. The name of your foreign key exceeds the maximum length of 64 characters.

1. 两个关键字段类型和/或大小不完全匹配。例如，如果一个是INT(10)key 字段需要INT(10)as well 而 not INT(11)or TINYINT。您可能需要使用确认字段大小SHOWCREATETABLE，因为查询浏览器有时会在视觉上只显示INTEGER了两个INT(10)和INT(11)。您还应该检查一个不是SIGNED，另一个是UNSIGNED。它们都需要完全相同。
2. 您尝试引用的键字段之一没有索引和/或不是主键。如果关系中的字段之一不是主键，则必须为该字段创建索引。
3. 外键名称是现有键的副本。检查您的外键名称在您的数据库中是否唯一。只需在密钥名称的末尾添加一些随机字符即可进行测试。
4. 您的一张或两张桌子都是一张MyISAM桌子。为了使用外键，表必须都是InnoDB. （实际上，如果两个表都存在，MyISAM那么您将不会收到错误消息——它只是不会创建键。）在查询浏览器中，您可以指定表类型。
5. 您已指定级联ONDELETESETNULL，但相关键字段设置为NOTNULL。您可以通过更改级联或将字段设置为允许NULL值来解决此问题。
6. 确保 Charset 和 Collat​​e 选项在表级别以及键列的单个字段级别都相同。
7. 您的外键列上有一个默认值（即 default=0）
8. 关系中的字段之一是组合（复合）键的一部分，没有自己的单独索引。即使该字段有一个索引作为组合键的一部分，您也必须仅为该键字段创建一个单独的索引，以便在约束中使用它。
9. 您的ALTER语句中有语法错误，或者您在关系中输入了错误的字段名称之一
10. 您的外键名称超过了 64 个字符的最大长度。

For more details, refer to: MySQL Error Number 1005 Can't create table

更多详情请参考：MySQL Error Number 1005 Can't create table

回答by happyhardik

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_checks 并创建数据库。

Just add the following to your script:

只需将以下内容添加到您的脚本中：

SET FOREIGN_KEY_CHECKS=0;

and it shall work.

它会起作用。

回答by zahid9i

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

当外键和引用键的类型或长度不同时，经常会发生这种情况。

回答by rajug

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

有时是由于主表被删除（可能是通过禁用foreign_key_checks），但外键CONSTRAINT在其他表中仍然存在。在我的情况下，我已经删除了表格并尝试重新创建它，但它对我来说抛出了同样的错误。

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

因此，尝试从所有表中删除所有外键 CONSTRAINT（如果有），然后更新或创建表。

回答by Rajiv Nair

Error Code: 1005

错误代码：1005

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

我有一个类似的问题，所以这里有一些我尝试过的东西（没有任何顺序，除了解决方案:)）

1. Changed the foreign key names (it didn't work)
2. Reduced the foreign key length
3. Verified the datatypes (darn nothing wrong)
4. Check indexes
5. Check the collations (everything fine, darn again)
6. Truncated the table, of no good use
7. Dropped the table and re-created
8. 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 / MySQL 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.

1. 更改了外键名称（不起作用）
2. 减少外键长度
3. 验证数据类型（该死的没有错）
4. 检查索引
5. 检查排序规则（一切正常，该死的）
6. 截断了表格，没有用
7. 删除表并重新创建
8. 尝试查看是否正在创建任何循环引用 --- 一切正常

9. 最后，我看到我打开了两个编辑器。一个在 PhpStorm (JetBrains) 和另一个 MySQL 工作台中。PhpStorm / MySQL Workbench 似乎创建了某种编辑锁。

   我关闭 PhpStorm 只是为了检查锁定是否是这种情况（它可能是相反的）。这解决了我的问题。

回答by user2258168

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 DEFAULT CHARACTER SET = 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 语句上意味着缺少一些代码。

回答by Csongor Halmai

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 不能告诉这样的表不存在......

回答by Yaina Villafa?es

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

回答by Brian Sanchez

check both tables has same schema InnoDB MyISAM. I made them all the same in my case InnoDB and worked

检查两个表是否具有相同的模式 InnoDB MyISAM。在我的案例 InnoDB 中，我使它们完全相同并工作

回答by Abdellah Alaoui

It happened in my case, because the name of the table being referenced in the constraint declaration wasn'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;