Error code 1005, SQL state HY000: Can't create table errno: 150

Malyo picture Malyo · Nov 9, 2011 · Viewed 14k times · Source

I'm trying to create a table but the script fails as soon as my netbeans errors the first table of DB.

How can this be solved?

CREATE TABLE filmy
(
    Film_Id int NOT NULL,
    Nazwa varchar(250),
    Adres varchar(250),
    Data_Utworzenia date,
    Komentarz varchar(250),
    Gat_Id int,
    Sub_Id int,
    Aut_Id int,
    User_Id int,

    Primary Key (Film_Id),
    CONSTRAINT fk_GatFilmy FOREIGN KEY (Gat_Id) REFERENCES gatunek(Gat_Id),
    CONSTRAINT fk_SubFilmy FOREIGN KEY (Sub_Id) REFERENCES subgatunek(Sub_Id),
    CONSTRAINT fk_AutFilmy FOREIGN KEY (Aut_Id) REFERENCES autor(Aut_Id),
    CONSTRAINT fk_UserFilmy FOREIGN KEY (User_Id) REFERENCES users(User_Id)
)

Answer

Marc B picture Marc B · Nov 9, 2011

Use show innodb status - buried in the output (around the middle) is a "last foreign key error" section. It'll explain exactly why the table creation failed.

usually it's due to a reference FK field not existing (typo, wrong table), or there's a field-type mismatch. FK-linked fields must match definitions exactly. A char(1) field can't be FK'd to a char(5) field, etc...

Note: In MySQL 5.5, the command for this is show engine innodb status (thanks kewpiedoll99)