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

Question

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

score 10 · Accepted Answer · edited Sep 19 '13 at 13:18

10

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)

edited Sep 19 '13 at 13:18

Enno Shioji

26,542
13
70
109

answered Nov 09 '11 at 19:26

Marc B

356,200
43
426
500

Thanks! I've found out that the problem was defining referenced to tables, after the references in ,,main'' table. – Malyo Nov 09 '11 at 19:47
1

Wow! `show innodb status` is priceless for debugging. Thank you very much! – barclay Feb 28 '12 at 15:35
4

or `show engine innodb status` if the former does not work, as it does not for me; i'm using 5.5.11 MySQL Community Server. – barclay Jun 01 '12 at 18:23

score 1 · Answer 2 · edited Dec 29 '11 at 00:36

Here is my solution:

CREATE TABLE filmy 
( 
    Film_Id           int           NOT NULL, 
    Nazwa             varchar(250)      NULL, 
    Adres             varchar(250)      NULL, 
    Data_Utworzenia   date              DEFAULT '0000-00-00', 
    Komentarz         varchar(250)      NULL, 
    Gat_Id            int               NULL, 
    Sub_Id            int               NULL, 
    Aut_Id            int               NULL, 
    User_Id           int               NULL, 
 Primary Key (Film_Id, Gat_Id, Sub_Id, Aut_Id, User_Id )
) ENGINE=INNODB;

Foreign key constraints are done after creating the gat, sub, aut & user or else the ide does not know have the two tables existing to make the table constraint a reality! Try: alter table filmy add constraint gatfilmy foreign key (gat_id) references gat(gat_id) on update restrict on delete restrict you have to keep consistent; either name the table gat or gatunek, it cannot be both. How will the cpu know which is gat or gatunek if you do not define them both? Now try with the rest constraints and remember you ahve to create all the tables before you can alter them!

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)

It was basicaly order problem :) – Malyo Dec 29 '11 at 13:09 — Malyo, Dec 29 '11 at 13:09

score 0 · Answer 3 · answered Sep 06 '13 at 17:49

May you used with this table name(filmy) in a relation to other table and then you dropped it. check any relation and remove every where you use with this table name or change your table name for example use "filmy1" I changed my table name then it worked.

I hope this work.

gbn · Answer 4 · 2011-11-09T19:28:38.227

0

The code 150 is a foreign key error.

One of the referenced tables or columns does not exist (yet, maybe later in your script) or doesn't match type/length/collation/charset. Comment them out in turn to see which one.

Or run separate ALTER TABLE commands after all your CREATEs have run

edited Nov 09 '11 at 19:28

answered Nov 09 '11 at 19:21

gbn

422,506
82
585
676

score 0 · Answer 5 · answered Nov 09 '11 at 19:34

0

I usually get that error when I try to add a foreign key for a column that doesn't have an index on it; I notice that none of your associated columns do in the SQL shown.

answered Nov 09 '11 at 19:34

James

318
1
5

Recent mysql versions will now create the required indexes for you, thankfully. – Marc B Nov 09 '11 at 19:41

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

5 Answers5

Linked