MySQL Foreign Key Constraint Error 150

Like a lot of software tools, MySQL error messages are sometimes not very precise, and sometimes not even that accurate. Last night during a database schema update, we ran into an issue creating a table with a foreign key constraint. Our tests had run successfully on several other MySQL 5.0 and 5.1 systems, so I wasn’t sure why it failed on this MySQL 5.0 instance. The actual error message was something like:

ERROR 1005 (HY000): Can't create table 'test.b' (errno: 150)

Looking up the error code confirmed that it was an issue with the foreign key constraint.

$ perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed

This suggested to me that there was something wrong with the syntax. But, then why did it work elsewhere?

Oftentimes, the problem is actually with the columns that are referenced in the constraint. A careful comparison of the two tables revealed no differences in the column definitions. However, the column definition doesn’t tell the whole story.

It would be painful if you had to declare the character set each time for every character-based column. So, MySQL lets you define the default character set for the table, and each character column will inherit that specification. The table inherits from the database setting and the database inherits the systemwide setting. So, visually inspecting the CREATE TABLE statement may not be sufficient.

In our case, the first table had been created when we had been using a systemwide default character set of latin1. We had since changed to using utf8. Since the columns that were being referenced in the constraint were varchars, the character set needed to be the same. This would not have been an issue, of course, if the columns had been integers or some other non character-based column.

The statements below can be used to recreate the error message. It’s more obvious in this example, because the default character set is explicitly stated. Default values are great, until they silently sneak up on you and gnaw your hand off.

CREATE TABLE a (
	id VARCHAR(10) NOT NULL,
	PRIMARY KEY (id)
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE b (
	id VARCHAR(10) NOT NULL,
	FOREIGN KEY (id) REFERENCES a(id),
	PRIMARY KEY (id)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;