MySQL change type of foreign key

To answer my own question, I could not discover a simpler way to do this. I ended up dropping all the foreign key constraints, changing the field types and then adding all the foreign key constraints back.

As R. Bemrose noted, using SET foreign_key_checks = 0; only helps when adding or changing data, but doesn't allow ALTER TABLE commands that would break foreign key constraints.

Here is my small contribution to this thread. Thanks to Daniel Schneller for inspiration and giving me a huge part of the solution!

set group_concat_max_len = 2048;
set @table_name = "YourTableName";
set @change = "bigint unsigned";
select distinct table_name,
       column_name,
       constraint_name,
       referenced_table_name,
       referenced_column_name,
       CONCAT(
           GROUP_CONCAT('ALTER TABLE ',table_name,' DROP FOREIGN KEY ',constraint_name SEPARATOR ';'),
           ';',
           GROUP_CONCAT('ALTER TABLE `',table_name,'` CHANGE `',column_name,'` `',column_name,'` ',@change SEPARATOR ';'),
           ';',
           CONCAT('ALTER TABLE `',@table_name,'` CHANGE `',referenced_column_name,'` `',referenced_column_name,'` ',@change),
           ';',
           GROUP_CONCAT('ALTER TABLE `',table_name,'` ADD CONSTRAINT `',constraint_name,'` FOREIGN KEY(',column_name,') REFERENCES ',referenced_table_name,'(',referenced_column_name,')' SEPARATOR ';')
       ) as query
from   INFORMATION_SCHEMA.key_column_usage
where  referenced_table_name is not null
   and referenced_column_name is not null
   and referenced_table_name = @table_name
group by referenced_table_name

By setting @table_name and @change you can generate a query. @table_name should be a table name of the table with the primary key (it will look for the tables that uses that column as a foreign key) and change its type to @change.

I had to change a few tables like that, so that worked like a charm. I just had to change @table_name and then perform a query.

To find out about the use of foreign key constraints, issue the following query on the INFORMATION_SCHEMA database:

select distinct table_name, 
       column_name, 
       constraint_name,  
       referenced_table_name, 
       referenced_column_name 
from   key_column_usage 
where  constraint_schema = 'XXX' 
   and referenced_table_name is not null 
   and referenced_column_name is not null;

Replace XXX with your schema name. This will give you a list of tables and columns that refer to other columns as foreign keys.

Unfortunately schema changes are non-transactional, so I fear you will indeed have to temporarily disable the foreign_key_checks for this operation. I recommend - if possible - to prevent connections from any clients during this phase to minimize the risk of accidental constraint violations.

As for the keys themselves: They will need to be dropped and recreated as well when you have changed the table data types.