MySQL外键，无法创建表(错误号:150) [英] MySQL Foreign Key, Can't create table (errno: 150)

问题描述

我正在尝试为我的系统构建数据库和表.但是我发现，如果不在代码中添加外键.没有错误.我使用了很多方法尝试使代码正常工作，但是仍然有错误.

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.

我正在使用MySQL 5.5.31，这里的代码是: 创建数据库TOS；

I am using MySQL 5.5.31, and the codes here: 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));

错误:

1005-无法创建表"TOS.BILL_ITEM"(错误号:150)

任何帮助将不胜感激.

推荐答案

非描述性错误150通常与外键数据类型或长度不匹配，或在父表的列上缺少索引有关.

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.

在表名Bill_Header中应该是大小写敏感的(应该为BILL_HEADER).
有关标识符区分大小写的MySQL文档:

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:

在MySQL中，数据库对应于数据目录中的目录.数据库中的每个表都对应于数据库目录中的至少一个文件(可能还有更多文件，具体取决于存储引擎).因此，基础操作系统的区分大小写在数据库和表名的区分大小写中发挥了作用.这意味着数据库和表名在Windows中不区分大小写，在大多数Unix版本中也不区分大小写.

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.

解决问题，它应该可以工作:

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

因为您的代码在SQLFiddle.com( http://sqlfiddle.com/#!2/08d1e)底层平台必须不区分大小写.

Since your code worked as is at SQLFiddle.com (http://sqlfiddle.com/#!2/08d1e) the underlying platform there must not be case-sensitive.

这篇关于MySQL外键，无法创建表(错误号:150)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！