Error can’t create table …
I was trying to add new table and alter another table to add new column and foreign key to the new table. Here’s basically what I had
CREATE TABLE `chapters` ( `chapter_id` int(10) unsigned NOT NULL auto_increment, `chapter_title` varchar(255) NOT NULL, PRIMARY KEY (`chapter_id`) ); ALTER TABLE `articles` ADD `chapter_id` int(10), ADD CONSTRAINT `articles_chapters` FOREIGN KEY (`chapter_id`) REFERENCES `chapters` (`chapter_id`) ;
When I execute the code, I got an error message something like Error can’t create table … (errno: 150). It turned out that the problem is on the alter table for article, I have chapter_id defined as int(10) and on chapters table I have it defined as unsigned int(10). Fixing the alter table to define chapter_id as unsigned int(10) fixed it.
Error on rename of …
I was using Sequel Pro to drop a foreign key by selecting the key and clicking on delete button. I failed to do so and got error on rename of. I’m not sure what Sequel Pro did (or what SQL code it runs behind the scene). However, I do know that this error happens when you try to drop a column that still have foreign key constraint. So if you try to drop column chapter_id on our example above, you’ll get error 150 error on rename. What I ended up doing was to run SQL code without the GUI tool and drop the column manually.
-- This code will produce error on rename of ALTER TABLE articles DROP chapter_id; -- What you should do is to drop the foreign key first ALTER TABLE articles DROP FOREIGN KEY articles_chapters; ALTER TABLE articles DROP chapter_id;
I hope this article helps you. As always, I welcome comments / questions / critics that will help me and other readers understand better.