MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Decoding 1005 on MySQL

with 17 comments

If you’re using referential integrity on MySQL, you can easily run into this error with the InnoDB engine. It frequently seems to occur with an ALTER TABLE statement. It can mean many things but typically it means the data types don’t match between the foreign key and primary key column. Likewise, it can mean one of the column data types disagrees in a multiple-column foreign to multiple-column primary key constraint.

The error by itself isn’t very handy. This is a sample:

ERROR 1005 (HY000): Can't create table 'sampledb.#sql-4a0_2' (errno: 150)

You can try SHOW WARNINGS but you’ll discover more about the error by running the following command as the root superuser:

mysql> SHOW engine innodb STATUS;

It returns a dump of the InnoDB’s activity. You can see it by unfolding the complete log, if you’re interested in the details. The significant part of the log to solve this type of problem is:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
100130 17:16:57 Error IN FOREIGN KEY CONSTRAINT OF TABLE sampledb/#sql-4a0_2:
FOREIGN KEY(member_type)
REFERENCES common_lookup(common_lookup_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.

While this is more information, it isn’t necessarily enough to solve the problem when you’re new to MySQL. The first place you should look is whether the data types agree between the foreign key and primary key columns.

The most common variation that I’ve run into is where the primary key column uses a int unsigned data type and the foreign key column uses an int data type. It’s quite nice that the InnoDB Engine stops this cold. Naturally, you fix it by changing the foreign key data type to match the int unsigned data type.

Written by maclochlainn

January 30th, 2010 at 6:58 pm

Posted in InnoDB,MySQL,sql