MySQL :: ERROR 1005: Can’t create table (errno: 150)

While importing a MySQL dump file, I got the error MySQL :: ERROR 1005: Can’t create table (errno: 150). I wrote this document as I think it may be helpful for the new MySQL users. While running a CREATE TABLE statement if MySQL reports an error number 1005 (error message refers to error 150), it is because a foreign key constraint was not correctly formed. To find the details of the error we can see the output of innodb status.

In MySQL client run the command:


mysql> show innodb status\G 

In LATEST FOREIGN KEY ERROR we can see the details of the error.
This problem occurred because the table creation order in the dump file is not correctly ordered for foreign keys. To import a dump file which gives the above error, follow the following steps:

1. First disable foreign key checking
   mysql> SET foreign_key_checks = 0;
2. Import the dump file
   mysql> SOURCE dump_file_name;
   
   File import in process:
   
   
   
3.  After import is done, enable foreign key checking again.
   mysql> SET foreign_key_checks = 1;

 That’s it, and we are done.

জয় আই অসম,

প্রণব শর্মা