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

I had the same error. In my case the reason for the error was that I had a ON DELETE SET NULL statement in the constraint while the field on which I put the constraint in its definition had a NOT NULL statement. Allowing NULL in the field solved the problem.

Answer 2

The column of PARENT table to which you are referring to from child table has to be unique. If it is not, cause an error no 150.

Answer 3

In some cases, you may encounter this error message if there are different engines between the relating tables. For example, a table may be using InnoDB while the other uses MyISAM. Both need to be same

Answer 4

After cruising through the answers above, and experimenting a bit, this is an effective way to solve Foreign Key errors in MySQL (1005 - error 150).

For the foreign key to be properly created, all MySQL asks for is:

• All referenced keys MUST have either PRIMARY or UNIQUE index.
• Referencing Column again MUST have identical data type to the Referenced column.

Satisfy these requirements and all will be well.

Answer 5

Make sure that the all tables can support foreign key - InnoDB engine

Answer 6

In most of the cases the problem is because of the ENGINE dIfference .If the parent is created by InnoDB then the referenced tables supposed to be created by MyISAM & vice versa