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

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.

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:

2) 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? Here is one way to do it.

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 FOREIGNKEY 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.