Sunday, March 22, 2009

Infamous Error 150 while creating a table in MySQL

I am working on a data migration and defining the schema. The application will eventually be implemented in Ruby on Rails, but instead of migrations, the client needed the sql dump. My CREATE TABLE statement was failing with errno 150, and SHOW ENGINE INNODB STATUS was not helpful. The Foreign Key section kept telling me :

Cannot resolve table name close to:
(id)) engine=innodb

I was completely zapped, both the related tables were INNODB tables, the columns were same type and size, indices were defined correctly. It was after sometime that I realized that the syntax for creating the foreign key was wrong. I was saying:

FOREIGN KEY (parent_id) REFERENCES parent.id

But it should be:
FOREIGN KEY (parent_id) REFERENCES parent(id)

Labels: ,