I described one problem we had with legacy application in text “tiny fun with TINYINT” in MySQL 5.7. To change data type from tinyint to int was at the end much bigger problem then we originally expected. Column was referenced in 5 different foreign keys in 5 other tables – one of them quite huge (~3.5 GB).
Some old answers on stackoverflow suggested that setting “SET foreign_key_checks=0;” will allow to change columns’ data type without necessity to manipulate with foreign keys. Unfortunately in MySQL 5.7 it does not work – it is even mentioned in documentation (https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html) – “However, even if
foreign_key_checks =, MySQL does not permit the creation of a foreign key constraint where a column references a nonmatching column type.”
Which we have seen when we tried it – we got error “Error Code: 1025. Error on rename of ‘./mydb/#sql-164a_4305’ to ‘./mydb/mytable’ (errno: 150 – Foreign key constraint is incorrectly formed)”
Therefore we had to:
- drop all foreign key which reference column we need to change
- change type on main column
- change types of all related columns referencing main column in foreign keys
- create all foreign keys again
Here is query which generates all “drop foreign key”, “alter table modify column” and “alter table add constraint” statement for tables with foreign keys referencing main column:
select concat('alter table `',ku.TABLE_NAME, '` drop foreign key `', ku.CONSTRAINT_NAME, '`;') as drop_fk_cmd, concat('alter table `',ku.TABLE_NAME, '` modify `', ku.COLUMN_NAME,'` int unsigned;') as alter_column_cmd, concat('alter table `',ku.TABLE_NAME, '` add constraint `', ku.CONSTRAINT_NAME, '` foreign key ( `', ku.COLUMN_NAME, '` ) references `', ku.REFERENCED_TABLE_NAME, '` ( `', ku.REFERENCED_COLUMN_NAME, '` ) ', if( rc.DELETE_RULE <> 'RESTRICT', concat(' on delete ',rc.DELETE_RULE),''), if( rc.UPDATE_RULE <> 'RESTRICT', concat(' on update ', rc.UPDATE_RULE),''),';') as create_fk_cmd from information_schema.KEY_COLUMN_USAGE ku join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on ku.CONSTRAINT_NAME = rc.CONSTRAINT_NAME where ku.REFERENCED_TABLE_NAME = 'your_table' and ku.REFERENCED_COLUMN_NAME = 'your_column'
If you want to use it – set proper data type in “alter_column_cmd”. Plus you need to add manually 2 command (1 and 3 – see below).
Scenario of the change:
- run command “use youdb;”
- run all drop foreign key command generated by query
- run “alter table modify column” for your main column
- run all “alter table modify column” commands generated by query
- run all “all table add constraint” commands generated by query