MySQL 外键，无法创建表 (errno: 150)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20166573/
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 Foreign Key, Can't create table (errno: 150)
提问by Confucius
I am trying to build the database and tables for my system. But I found that if I don't add the foreign key in the codes. There is no error. I've used many method try to make the codes works, but it still have error.
我正在尝试为我的系统构建数据库和表。但是我发现如果我不在代码中添加外键。没有错误。我使用了很多方法尝试使代码正常工作，但它仍然有错误。
I am using MySQL 5.5.31, and the codes here: CREATE DATABASE TOS;
我使用的是 MySQL 5.5.31，这里的代码是：CREATE DATABASE TOS;
DROP TABLE TOS.USER CASCADE;
DROP TABLE TOS.BILL_HEADER CASCADE;
DROP TABLE TOS.TOY CASCADE;
CREATE TABLE TOS.USER
(User Char(8),
Name Char(10),
Type Char(1),
Password Char(12),
PRIMARY KEY(User));
CREATE TABLE TOS.BILL_HEADER
(Bill_No Char(10),
CTime DateTime,
No_Of INTEGER,
Cus_No Char(5),
DTime DateTime,
PRIMARY KEY(Bill_No));
CREATE TABLE TOS.TOY
(Toy_Id Char(10),
FullN Char(50),
ShortN Char(20),
Descrip Char(20),
Price DECIMAL,
Avail Char(1),
Cat Char(1),
PRIMARY KEY(Toy_Id));
CREATE TABLE TOS.BILL_ITEM
(Bill_No Char(10),
BSeq_No INTEGER,
Toy_Id Char(10),
OTime DateTime,
Quan INT,
DCondition Char(1),
PRIMARY KEY(Bill_No,BSeq_No),
FOREIGN KEY(Bill_No) REFERENCES TOS.Bill_Header(Bill_No),
FOREIGN KEY(Toy_Id) REFERENCES TOS.TOY(Toy_Id));
Error:
错误：
1005 - Can't create table 'TOS.BILL_ITEM' (errno: 150)
1005 - 无法创建表“TOS.BILL_ITEM”（错误号：150）
Any help would be greatly appreciated.
任何帮助将不胜感激。
回答by Michael Berkowski
The non-descript error 150 is usually related to foreign key data type or length mismatches, or a missing index on the parent table's column.
非描述性错误 150 通常与外键数据类型或长度不匹配或父表列上缺少索引有关。
This look s to be a matter of case sensitivity in the table name Bill_Header(should be BILL_HEADER).
From the MySQL docs on identifier case sensitivity:
这看起来是表名中区分大小写的问题Bill_Header（应该是BILL_HEADER）。
来自关于标识符区分大小写的 MySQL 文档：
In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database and table names. This means database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix.
在 MySQL 中，数据库对应于数据目录中的目录。数据库中的每个表对应于数据库目录中的至少一个文件（可能更多，取决于存储引擎）。因此，底层操作系统的区分大小写在数据库和表名称的区分大小写中起作用。这意味着数据库和表名在 Windows 中不区分大小写，而在大多数 Unix 版本中区分大小写。
Fix the case and it should work:
修复案例，它应该可以工作：
CREATE TABLE TOS.BILL_ITEM
(Bill_No Char(10),
BSeq_No INTEGER,
Toy_Id Char(10),
OTime DateTime,
Quan INT,
DCondition Char(1),
PRIMARY KEY(Bill_No,BSeq_No),
FOREIGN KEY(Bill_No) REFERENCES TOS.BILL_HEADER(Bill_No),
# Here-----------------------------^^^^^^^^^^^^^^
FOREIGN KEY(Toy_Id) REFERENCES TOS.TOY(Toy_Id));
Since your code worked as is at SQLFiddle.com (http://sqlfiddle.com/#!2/08d1e) the underlying platform there must not be case-sensitive.
由于您的代码在 SQLFiddle.com ( http://sqlfiddle.com/#!2/08d1e) 上工作，因此底层平台不能区分大小写。
回答by pilavdzice
Above answer is correct, but this error can also happen if the table your foreign key is referencing is MyISAM instead of innoDB.
上面的答案是正确的，但如果您的外键引用的表是 MyISAM 而不是 innoDB，也会发生此错误。
