Flite Careers

Improved Foreign Key Error Messages in MySQL 5.6

It took a little more than 10 years, but as of version 5.6 MySQL is finally using error code 1215! Since MySQL first introduced foreign key support more than 10 years ago in version 3.23.44, all the way through version 5.5, the server has always given a generic 1005 error when you try to add an invalid foreign key.

Here’s an example in MySQL 5.5, where the parent key is unsigned and the child key is signed:

1
2
3
4
5
6
7
8
9
10

mysql> create table parent (
    ->   id int unsigned primary key
    -> ) engine = innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> create table child (
    ->   id int primary key,
    ->   parent_id int,
    ->   foreign key fk_child_to_parent (parent_id) references parent (id)  
    -> ) engine = innodb;
ERROR 1005 (HY000): Can't create table 'test.child' (errno: 150)

Error 1005 doesn’t give me any clue about why the DDL failed. If I run SHOW ENGINE INNODB STATUS and look at the “LATEST FOREIGN KEY ERROR” section I can get more details. But how would I even know to look there based on the generic 1005 error?

If I run the same DDL in MySQL 5.6 I get the 1215 error:

1
2
3
4
5
6
7
8
9
10

mysql> create table parent (
    ->   id int unsigned primary key
    -> ) engine = innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> create table child (
    ->   id int primary key,
    ->   parent_id int,
    ->   foreign key fk_child_to_parent (parent_id) references parent (id)  
    -> ) engine = innodb;
ERROR 1215 (HY000): Cannot add foreign key constraint

It doesn’t tell me exactly what’s wrong with the foreign key but at least the 1215 error tells me that the foreign key is the problem.

I can run SHOW ENGINE INNODB STATUS and look at the “LATEST FOREIGN KEY ERROR” section to get a little more information:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2013-11-01 17:23:57 144255000 Error in foreign key constraint of table test/child:
foreign key fk_child_to_parent (parent_id) references parent (id)  
) engine = innodb:
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.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

That narrows it down a bit. It’s either a non-indexed parent key, or a data type mismatch. In my case it’s a data type mismatch.

In summary, it’s nice to see MySQL finally using error code 1215, but tracking down foreign key DDL errors remains tedious, so there’s still room for improvement.

MySQL
Comments
Back to Top

Posted by Ike Walker

Comments