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

Error 150 means you have a problem with your foreign key. Possibly the key on the foreign table isn't the exact same type?

Answer 2

150 is usually a foreign key error. Are you sure the keywords table exist?

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

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 right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to error 150 in the error message.