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.