MySQL ERROR 1005 (HY000): 无法创建表 (errno: 150)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11045279/
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
ERROR 1005 (HY000): Can't create table (errno: 150)
提问by Boon
I get an error when I try to create a table in mysql.
当我尝试在 mysql 中创建表时出现错误。
Any tips on resolving it?
有什么解决办法吗？
create table stock_in(
ind int not null auto_increment,
itemcode varchar(10) not null,
quantity int not null,
description text not null,
sales_ref int not null default -1,
return_outwards_ref int not null default -1,
stock_in_receipt_ref int not null default -1,
date text not null,
time text not null,
username text not null,
foreign key (sales_ref) references sales (receiptno),
foreign key (return_outwards_ref) references returnoutwards(ind),
primary key (ind)
);
The Error:
错误：
ERROR 1005 (HY000): Can't create table 'posinventory.stock_in' (errno: 150)
回答by Bjoern
Check out the MySQL manual about foreign key constrains:
查看有关外键约束的 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 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.
如果您重新创建一个被删除的表，它必须有一个符合引用它的外键约束的定义。如前所述，它必须具有正确的列名和类型，并且必须在所引用的键上具有索引。如果这些都不满足，MySQL 将返回错误号 1005 并在错误消息中引用错误 150。
A few ideas:
一些想法：
- Better drop the tables and create it new with a well formed syntax.
- Make sure to add
ENGINE=InnoDB;to yourCREATE TABLE- command. - Make sure InnoDB is enabled on your MySQL server. To verify this, try this command:
SHOW VARIABLES LIKE 'have_innodb';- if it returns a YES, then InnoDB is enabled. - Check your command for upper- and lowercases in table- and fieldnames.
- Check this not only one the table you want to create, but also on the tables the foreign keys are referring to.
- Make sure your referred tables are properly indexed.
- 最好删除表格并使用格式良好的语法创建新表格。
- 确保添加
ENGINE=InnoDB;到您的CREATE TABLE- 命令中。 - 确保在您的 MySQL 服务器上启用了 InnoDB。要验证这一点，请尝试以下命令：
SHOW VARIABLES LIKE 'have_innodb';- 如果返回 YES，则启用 InnoDB。 - 检查表名和字段名中的大写和小写命令。
- 不仅要检查要创建的表，还要检查外键所指的表。
- 确保您引用的表已正确编入索引。
回答by user1520978
Had exactly the same problem. The source of it is in types for foreign key and reference.
有完全相同的问题。它的来源是外键和引用的类型。
Foreign key:
外键：
fer_id SMALLINT NOT NULL
and the origin field (refernce to which we provide):
和原点字段（我们提供的参考）：
id INT(11) UNSIGNED NOT NULL
I've just make the fer_id INT(11) UNSIGNED as well. Magically works!
我也刚刚将 fer_id 设为 INT(11) UNSIGNED。神奇的作品！
回答by user2285323
If you restore your database backup you may temporary disable foreign keys check by inserting these strings in the begining of .sql file:
如果您恢复数据库备份，您可以通过在 .sql 文件的开头插入这些字符串来临时禁用外键检查：
/*!40030 SET NAMES UTF8 */;
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
回答by Jacques Amar
On top of other things, you need to make sure both tables/fields match CHARSET settings. A UTF type field can be substantially different size than a 'latin1'. SHOW CREATE TABLE on both tables will show you if there is a mismatch
最重要的是，您需要确保两个表/字段都匹配 CHARSET 设置。UTF 类型字段的大小可能与“latin1”有很大不同。两个表上的 SHOW CREATE TABLE 将显示是否存在不匹配
回答by Withfriendship Hiox
One of the reason would be the foreign key column data type and length should be same as the referring table key column. For example the referring table column is mediumint(11) and the foreign key column is int(11) means error will occur while creating a table with foreign key.
原因之一是外键列数据类型和长度应与引用表键列相同。例如，引用表列是 mediumint(11)，外键列是 int(11) 意味着在创建带有外键的表时会发生错误。
