Change a column which is or has a foreign key in MySQL

When trying to change(alter) a column which is a foreign or has a foreign in MySQL, an error info like below shows up:

Error
SQL query:
ALTER TABLE `city` CHANGE `CountryCode` `CountryCode` CHAR( 3 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT ''
MySQL said:
#1025 - Error on rename of './test/#sql-82d_2cb' to './test/city' (errno: 150)

Command
show create table city;
shows that the column "CountryCode" in "city" table is a foreign key pointing to column "Code" in the "country" table:

CREATE TABLE `city` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`CountryCode` char(3) CHARACTER SET latin1 NOT NULL DEFAULT '',
`District` char(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

An easy quick fix for this is to set the foreign_key_checks to 0 manually before the command to alter table, temporarily of course:

SET foreign_key_checks = 0;
ALTER TABLE `city` CHANGE `CountryCode` `CountryCode` CHAR( 3 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '';
SET foreign_key_checks = 1;

This way, the commands are executed smoothly without any error info!

English

Tags:

Add new comment