Thursday, 10 September 2015

MySQL’s generic “errno 150” message

MySQL's generic "errno 150" message "means that a foreign key constraint was not correctly formed." As you probably already know if you are reading this page, the generic "errno: 150" error message is really unhelpful. However:

You can get the actual error message by running SHOW ENGINE INNODB STATUS; and then looking for LATEST FOREIGN KEY ERROR in the output.

For example, this attempt to create a foreign key constraint:

CREATE TABLE t1  (id INTEGER);    CREATE TABLE t2  (t1_id INTEGER,   CONSTRAINT FOREIGN KEY (t1_id) REFERENCES t1 (id));

fails with the error Can't create table 'test.t2' (errno: 150). That doesn't tell anyone anything useful other than that it's a foreign key problem. But run SHOW ENGINE INNODB STATUS; and it will say:

------------------------  LATEST FOREIGN KEY ERROR  ------------------------  130811 23:36:38 Error in foreign key constraint of table test/t2:  FOREIGN KEY (t1_id) REFERENCES t1 (id)):  Cannot find an index in the referenced table where the  referenced columns appear as the first columns, or column types  in the table and the referenced table do not match for constraint.

No comments:

Post a Comment