MySQL Foreign Key, Can't create table (errno: 150)

Question

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;

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)

Any help would be greatly appreciated.

Lactiferous

It does appear to work if I remove the DROP statements.. sqlfiddle.com/#!2/08d1e – Territus

Usually, err 150 is a foreign key error, due to a data type and length mismatch between the column and relation, or a missing index on the primary column. You do have all the correct types and PK indexes defined though. – Territus

Could you try to remove only one of the foreign keys to narrow the problem? – Welt

Answer 1

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.

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:

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

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

Territus

In my case there was field collate mismatch between parent and child table. – Kana

Another subtle issue is that the character set must match. I just ran into this where the table I was referencing was utf8, but it was trying to create a new constraint on a latin-1 table. Once I specified the ut8 in the create table all was well. – Mayfair

also check for signed vs. unsigned mismatch on a foreign key field – Illiterate

Answer 2

Above answer is correct, but this error can also happen if the table your foreign key is referencing is MyISAM instead of innoDB.

Captain

Or if the parent table has a DEFAULT CHARSET specified and the child-table either does not have it specified or specifies a different DEFAULT CHARSET. – Ranunculus