MySQL: Can't Create Table (Errno: 150)

MySQL: Can't create table (errno: 150)

From the MySQL - FOREIGN KEY Constraints Documentation:

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 correct column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns Error 1005 and refers to Error 150 in the error message, which means that a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails due to Error 150, this means that a foreign key definition would be incorrectly formed for the altered table.

MySQL Foreign Key, Can't create table (errno: 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.

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.

MySQL. Can't create table errno 150

table1.field1 has no index defined on it.

It is required to place a FOREIGN KEY constraint on field1.

With this:

 CREATE  TABLE IF NOT EXISTS `testdb`.`table1` (
   `id` INT UNSIGNED NOT NULL ,
   `field1` VARCHAR(50) NULL ,
   KEY ix_table1_field1 (field1),
   PRIMARY KEY (`id`) )
 ENGINE = InnoDB;

Everything should then work as expected.

Can't create table `db`.`WRITER` (errno: 150 Foreign key constraint is incorrectly formed)

Your definition of SUMMARY_LANG is wrong

CREATE TABLE SUMMARY_LANG(
    Bno int not null primary key auto_increment,
    Language text,
    FOREIGN KEY (Bno) REFERENCES BOOKS(Bno)  <-- remove this reference
);

Remove the foreign key, because this is a Table that is used only as reference number to another table also called a helper table, because the text would be redundant in the referenced table.

But i can't see any column that references language.

So add a column to BOOKS, where you add the reference to SUMMARY_LANG and when you add new rows SUMMARY_LANG you won't get any errors anymore.

So the new tables can be like this

CREATE TABLE BOOKS (
    Bno INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    Title TEXT,
    PDate DATE,
    Cno INT,
    Cname TEXT,
    SNno int,
    FOREIGN KEY (SNno)
        REFERENCES SUMMARY_LANG (SNno)
);
CREATE TABLE SUMMARY_LANG(
    SNno int not null primary key auto_increment,
    Language text
);

---

Related Topics