MySQL errno 150

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 that 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 engine MyISAM, while table B has engine InnoDB. Make sure they are all tables are using the InnoDB engine.

Published: Friday, 16 March 2012