MySQL: Error 1005 Can’t create table (errno: 150)

This error has been driving me insane all morning. Every time I was trying to create a new InnoDB table with a foreign key constraint I would get the following error:

Error 1005 Can’t create table ‘.\mydb\#company.frm’ (errno: 150)

My SQL looked something like this:

CREATE TABLE company (
	`id` INT(32) AUTO_INCREMENT NOT NULL,
	`name` VARCHAR(50),
	`cfo` VARCHAR(50),
	`ceo` VARCHAR(50),
	`address` text,
	`tel` VARCHAR(15),
	`fax` VARCHAR(15),
	`email` VARCHAR(50),
	`general_type` enum('broker', 'manufacturer', 'wholesaler', 'service', 'distributor', 'retailer', 'healthcare', 'processor'),
	`specific_type` VARCHAR(50),
	`lender_id` INT(32),
	PRIMARY KEY (id),
	CONSTRAINT `fk_lender` FOREIGN KEY (`lender_id`) REFERENCES `client` (`lender_id`) ON UPDATE CASCADE,
  	CONSTRAINT `fk_specific_type` FOREIGN KEY (`specific_type`) REFERENCES `specific_type` (`specific_type`) ON UPDATE CASCADE
) ENGINE = InnoDB

I kept getting that damn error every single time. At first I figured this was a OS level file access permission issue. But this is not the case – it is just a silly, misleading message. I started googling for it and found some good tips on how to avoid this error at VerySimple Dev Blog. I spent over two hours testing all possible solutions listed in that post. Both tables – client and specific_type were InnoDB. The respective keys were the same type. The charset and collate attributes were set to default in both. And then it hit me.

It turns out that my problem was simple – you need to have an index on every field that will be a foreign key. I kinda figured that this would happen automatically, like with primary key, but it doesn’t. You need to create the index manually. My updated query looks like this:

CREATE TABLE `company` (
	`id` INT(32) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50),
	`cfo` VARCHAR(50),
	`ceo` VARCHAR(50),
	`address` text,
	`city` VARCHAR(50),
	`state` VARCHAR(50),
	`zip` CHAR(2),
	`tel` VARCHAR(15),
	`fax` VARCHAR(15),
	`email` VARCHAR(50),
	`general_type` enum('broker','manufacturer','wholesaler','service','distributor','retailer','healthcare','processor') NOT NULL DEFAULT 'broker',
	`specific_type` VARCHAR(50) NOT NULL,
	`lender_id` INT(32) NOT NULL,
	PRIMARY KEY  (`id`),
	KEY `specific_type` (`specific_type`),
	KEY `lender_id` (`lender_id`),
	CONSTRAINT `fk_lender` FOREIGN KEY (`lender_id`) REFERENCES `client` (`id`) ON UPDATE CASCADE,
	CONSTRAINT `fk_specific_type` FOREIGN KEY (`specific_type`) REFERENCES `specific_type` (`specific_type`) ON UPDATE CASCADE
) TYPE=InnoDB

Somehow this escaped my attention. It’s probably because lately was I simply adding foreign key constraints to existing tables after converting them to InnoDB from MyIsam. It worked fine because in most cases these tables already had indexes on the “foreign key” fields – there were just no constraints there.

So next time you are creating a InnoDB table, remember – put index on every field that will become a foreign key, unless of course you like to deal with overly cryptic error messages.

Update 07/09/2007 07:45:04 PM

I just tested this on MySQL 5.0 and the lack of index doesn’t cause this error. So one way to avoid a major headache is to upgrade to 5.0 or higher.

Here is one more tip I discovered when debugging this stuff – whenever you get the 1005 error, run the following SQL statement from the console:

SHOW InnoDB STATUS

You will get a very verbose report on the status of your tables – and one of the sections of this report is LATEST FOREIGN KEY ERROR. Locate that section and you might find out what exactly is wrong with your statement.

[tags]mysql, error 1005, errno 150, can’t create table, database, mysql error, innodb, foreign key[/tags]

This entry was posted in programming and tagged . Bookmark the permalink.



5 Responses to MySQL: Error 1005 Can’t create table (errno: 150)

  1. Jason UNITED STATES Mozilla Firefox Windows says:

    Thanks for stopping by – I pushed up this no-index situation to the #2 spot on my troubleshooting list and added your tip that you do have to have an index on both fields. Thanks for letting me know and hopefully it will save somebody from losing more hair!

    Reply  |  Quote
  2. Luke UNITED STATES Mozilla Firefox Windows says:

    [quote comment="5143"]Thanks for letting me know and hopefully it will save somebody from losing more hair![/quote]

    I hope so. This is one of the least helpful error messages I ever got from MySQL. I almost gave up on having foreign key constraints on these tables.

    Reply  |  Quote
  3. Luke UNITED STATES Mozilla Firefox Windows says:

    Oh, btw – I updated the post with two more tips:

    MySQL 5.0 creates the index on a foreign key field automatically – just like for primary key, so this issue does not occur.

    Also

    SHOW InnoDB STATUS

    is very useful when debugging. Among other things it will let you know what was the last foreign key constraint error.

    Maybe you could add these tips to your site as well. :)

    Reply  |  Quote
  4. JCCyC BRAZIL Mozilla Firefox Fedora Linux says:

    Man. I was burned by this just today. It turned out the integer field in one table was unsigned and, in the other, signed.

    Reply  |  Quote
  5. Dmitriy RUSSIAN FEDERATION Mozilla Firefox Windows says:

    I had the same problem. I added index for field, but the error still was ocuring, then look at reference field and saw that have a ‘unsigned field’. Changed field on unsigned and did work well

    Reply  |  Quote

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>