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've corrected the problem by making the variable accept null

ALTER TABLE `ajout_norme` 
CHANGE `type_norme_code` `type_norme_code` VARCHAR( 2 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL

Answer 2

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 correct column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns Error 1005 and refers to Error 150 in the error message, which means that a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails due to Error 150, this means that a foreign key definition would be incorrectly formed for the altered table.

Answer 3

If the PK table is created in one CHARSET and then you create FK table in another CHARSET..then also you might get this error...I too got this error but after changing the charset to PK charset then it got executed without errors

create table users
(
------------
-------------
)DEFAULT CHARSET=latin1;


create table Emp
(
---------
---------
---------
FOREIGN KEY (userid) REFERENCES users(id) on update cascade on delete cascade)ENGINE=InnoDB, DEFAULT CHARSET=latin1;

Answer 4

Please make sure both your primary key column and referenced column have the same data types and attributes (unsigned, binary, unsigned zerofill etc).

Answer 5

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 6

Data types must match exactly. If you are dealing with varchar types, the tables must use the same collation.