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…
|
1 2 3 4 |
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`) |