MySQL – Alter table-change datatype of column with foreign key

Schema alteration is a big headache especially when it has already reached to production and things get worse when the relations are involved.
Below is a short example for altering tables having foreign key constraints.

SQL for tables used in example:

create database temp;use temp;

drop table if exists city; drop table if exists country;
create table country ( c_id mediumint not null auto_increment primary key, c_name varchar(10)) engine=innodb;
insert into country (c_name) values ('india'),('uk'),('us');

create table city (city_id mediumint not null auto_increment primary key, country_id mediumint, city_name varchar(20), constraint fk_country_id foreign key (country_id) references country(c_id)) engine=innodb;
insert into city (country_id, city_name) values (1,'mumbai'), (2,'ahmedabad'), (2,'new york'), (2, 'la'), (3, 'london'), (3,'Birmingham');

We want to change the datatype of primary key c_id from mediumint to int.
Now, while you proceed to change the datatype of column in relation, you observe a typical mysql error.

ERROR 1025 (HY000): Error on rename of

mysql> alter table country modify column c_id int not null auto_increment;

ERROR 1025 (HY000): Error on rename of '.\temp\#sql-248_4' to '.\temp\country' (errno: 150)

“Error on the rename” doesn’t suggest you anything though you have a way out to know the actual error:

1) On your shell prompt fire:
$] perror 150

MySQL error code 150: Foreign key constraint is incorrectly formed

For more details you may look into last foreign key error section of innodb status:

In this case specifically you should check innodb status:

mysql> show engine innodb status;
 ...
 -------------------------
 LATEST FOREIGN KEY ERROR
 -------------------------
 120422 13:11:47 Error in foreign key constraint of table temp/city:
 there is no index in referenced table which would contain
 the columns as the first columns, or the data types in the
 referenced table do not match the ones in table. Constraint:
 ,
 CONSTRAINT "fk_country_id" FOREIGN KEY ("country_id") REFERENCES "country" ("c_id")
 The index in the foreign key in table is "fk_country_id"
 ...

This clearly explains you the reason for error in table alteration. InnoDB does not permit the creation of a foreign key constraint where a column references a nonmatching column type. This restriction applies while you alter the parent or child table with Foreign Key Constraint. So how ‘d you proceed?

The steps to follow are:
– Alter all the referencing tables and remove Foreign Key relations.
– Alter all the referencing and referenced tables and modify column to new datatype.
– Alter all referencing tables create Foreign Key relations.

To ease out the task execute following SQL and you will get your queries that will help you changing the datatype step by step. You have to provide the parent table name and column that’s being referenced along with the datatype you want to modify to.

command for drop foreign key relation

select  concat('ALTER TABLE ', table_name,' DROP FOREIGN KEY ',constraint_name,';') drop_fk_syntax from information_schema.key_column_usage where constraint_schema = database() and referenced_table_name ='country' and referenced_column_name ='c_id';
+--------------------------------------------------+
| drop_fk_syntax                                   |
+--------------------------------------------------+
| ALTER TABLE city DROP FOREIGN KEY fk_country_id; |
+--------------------------------------------------+

Command for apply column definition change to parent table

SELECT  CONCAT('ALTER TABLE ', referenced_table_name,' MODIFY COLUMN ', referenced_column_name, ' bigint not null auto_increment;') modify_column  from information_schema.key_column_usage where constraint_schema = database() and referenced_table_name ='country' and referenced_column_name ='c_id';
+---------------------------------------------------------------------------+
| modify_column                                                             |
+---------------------------------------------------------------------------+
| ALTER TABLE country MODIFY COLUMN c_id bigint not null auto_increment;    |
+---------------------------------------------------------------------------+

Command for apply column definition change to child tables

select  concat('ALTER TABLE ', table_name,' MODIFY COLUMN ', column_name, ' bigint not null;') modify_column  from information_schema.key_column_usage where constraint_schema = database() and referenced_table_name ='country' and referenced_column_name ='c_id';
+------------------------------------------------------------+
| modify_column                                              |
+------------------------------------------------------------+
| ALTER TABLE city MODIFY COLUMN country_id bigint not null; |
+------------------------------------------------------------+

Command for restoring foreignkey relation

select concat('ALTER TABLE ',table_name,' ADD CONSTRAINT ',constraint_name ,' FOREIGN KEY (', column_name, ') REFERENCES ', referenced_table_name , '(', referenced_column_name,');') create_fk_syntax from information_schema.key_column_usage where constraint_schema = database() and referenced_table_name ='country' and referenced_column_name ='c_id';
+--------------------------------------------------------------------------------------------------+
| create_fk_syntax                                                                                 |
+--------------------------------------------------------------------------------------------------+
| ALTER TABLE city ADD CONSTRAINT fk_country_id FOREIGN KEY (country_id) REFERENCES country(c_id); |
+--------------------------------------------------------------------------------------------------+

Execute above queries in sequence to modify your schema.

Always remember: “backup saves your life like no one else”.
Share your better ways.