MySQL errno 150

Published: Friday, 16 March 2012
mysql

MySQL Can’t create table: errno 150

This error message could be due to the following:

Table referenced by the FOREIGN KEY does not exist

mysql> create table B (
    ->   id int NOT NULL,
    ->   a_id int NOT NULL,
    ->   PRIMARY KEY (id),
    ->   FOREIGN KEY (a_id) REFERENCES A(id)
    -> ) engine=InnoDB;
ERROR 1005 (HY000): Can't create table 'demo.B' (errno: 150)

Check table A exists.

Table engines are incompatible

mysql> create table A (
    ->   id int NOT NULL,
    ->   PRIMARY KEY (id)
    -> ) engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> create table B (
    ->   id int NOT NULL,
    ->   a_id int NOT NULL,
    ->   PRIMARY KEY (id),
    ->   FOREIGN KEY (a_id) REFERENCES A(id)
    -> ) engine=InnoDB;
ERROR 1005 (HY000): Can't create table 'demo.B' (errno: 150)

In the above example table A has storage engine MyISAM, while table B has engine InnoDB. Make sure all tables are using the InnoDB engine.