Renaming A MySQL Column With A Foreign Key Constraint

This is more of a note to myself, but blogged in the hope it will help someone else using MySQL.

I had to rename a column in a MySQL database earlier. However the column had a foreign key on it, so I was getting the dreaded error 150, warning me of a foreign key constraint.

The soltuion is to first drop the foreign key, then change the column, finally adding the constraint back in.

The SQL will look something like this…

ALTER TABLE `mytable`
DROP FOREIGN KEY `mytable_oldname_fk`,
CHANGE COLUMN oldname newname INT(11) DEFAULT NULL,
ADD CONSTRAINT `mytable_newname_fk` FOREIGN KEY (`newname`) REFERENCES `anothertable` (`id`)