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

Question

I am trying to import a .sql file and its failing on creating tables.

Here's the query that fails:

CREATE TABLE `data` (
`id` int(10) unsigned NOT NULL,
`name` varchar(100) NOT NULL,
`value` varchar(15) NOT NULL,
UNIQUE KEY `id` (`id`,`name`),
CONSTRAINT `data_ibfk_1` FOREIGN KEY (`id`) REFERENCES `keywords` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;    

I exported the .sql from the the same database, I dropped all the tables and now im trying to import it, why is it failing?

MySQL: Can't create table './dbname/data.frm' (errno: 150)

Answer 1

Create the table without foreign key, then set the foreign key separately.

Answer 2

I think all these answers while correct are misleading to the question.

The actual answer is this before you start a restore, if you're restoring a dump file with foreign keys:

SET FOREIGN_KEY_CHECKS=0;

because naturally the restore will be creating some constraints before the foreign table even exists.

Answer 3

Error no. 150 means a foreign key constraint failure. You are probably creating this table before the table the foreign key depends on (table keywords). Create that table first and it should work fine.

If it doesn't, remove the foreign key statement and add it after the table is created - you will get a more meaningful error message about the specific constraint failure.

Answer 4

Sometimes MySQL is just super stupid - i can understand the reason cause of foreign-keys.. but in my case, i have just dropped the whole database, and i still get the error... why? i mean, there is no database anymore... and the sql-user i'm using has no access to any other db's on the server... i mean, the server is "empty" for the current user and i still get this error? Sorry but i guess MySQL is lying to me... but i can deal with it :) Just add these two lines of SQL around your fucky statement:

SET FOREIGN_KEY_CHECKS = 0;
# some code that gives you errno: 150
SET FOREIGN_KEY_CHECKS = 1;

Now the sql should be executed... If you really have a foreign-key problem, it would show up to you by the line where you will enable the checks again - this will fail then.. but my server is just quiet :)

Answer 5

Change the engines of your tables, only innoDB supports foreign keys

Answer 6

In my case. I had problems with engine and charset because my Hosting server change settings and my new tables was MyISAM but my old tables are InnoDB. Just i changed.