Terminally Incoherent

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.

SHOW InnoDB STATUS

This entry was posted on Monday, July 9th, 2007 at 2:57 pm and is filed under technology, programming, mysql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Related Posts: