MySQL change type of foreign key

Question

I am using MySQL and I have a table with an index that is used as a foreign key in many other tables. I want to change the data type of the index (from signed to unsigned integer) , what is the best way to do this?

I tried altering the data type on the index field, but that fails because it is being used as a foreign key for other tables. I tried altering the data type on one of the foreign keys, but that failed because it didn't match the data type of the index.

I suppose that I could manually drop all of the foreign key constraints, change the data types and add the constraints back, but this would be a lot of work because I have a lot of tables using this index as a foreign key. Is there a way to turn off foreign key constraints temporarily while making a change? Also, is there a way to get a list of all the fields referencing the index as a foreign key?

Update: I tried modifying the one foreign key after turning off foreign key checks, but it doesn't seem to be turning off the checks:

SET foreign_key_checks = 0;

ALTER TABLE `escolaterrafir`.`t23_aluno` MODIFY COLUMN `a21_saida_id` INTEGER DEFAULT NULL;

Here's the error:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
090506 11:57:34 Error in foreign key constraint of table escolaterrafir/t23_aluno:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match to the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
  CONSTRAINT FK_t23_aluno_8 FOREIGN KEY (a21_saida_id) REFERENCES t21_turma (A21_ID)

Definition of the index table:

DROP TABLE IF EXISTS `escolaterrafir`.`t21_turma`;
CREATE TABLE  `escolaterrafir`.`t21_turma` (
  `A21_ID` int(10) unsigned NOT NULL auto_increment,
  ...
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1;

and the table that has the foreign key that points to it:

DROP TABLE IF EXISTS `escolaterrafir`.`t23_aluno`;
CREATE TABLE  `escolaterrafir`.`t23_aluno` (
  ...
  `a21_saida_id` int(10) unsigned default NULL,
  ...
  KEY `Index_7` (`a23_id_pedagogica`),
  ...
  CONSTRAINT `FK_t23_aluno_8` FOREIGN KEY (`a21_saida_id`) REFERENCES `t21_turma` (`A21_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=387 DEFAULT CHARSET=latin1;

Tremble

Answer 1

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.

Tremble

I had the same problem, to solve it I: mysqldump'ed the database to a text file, changed the concerned columns and re-imported it. I think that's the easiest/quickest way. – Unhandsome

Answer 2

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.

Aircraftman

You should mention that you are using the INFORMATION_SCHEMA database, which holds the definitions for all the regular databases, to make these changes. I think you should also add and TABLE_SCHEMA = my_database_name to the where clause, or else your query will affect tables of the same name in all databases, where "my_database_name" would be the name of database where your table resides. – Houdan

Appending to my previous comment, perhaps CONSTRAINT_SCHEMA rather than TABLE_SCHEMA is the thing you need to do?? – Houdan

Had to replace: CONCAT('ALTER TABLE `',@table_name,'` CHANGE `id` `id` ',@change), to CONCAT('ALTER TABLE `',@table_name,'` CHANGE `',column_name,'` `',column_name,'` ',@change), and from key_column_usage to from INFORMATION_SCHEMA.KEY_COLUMN_USAGE – Olivaolivaceous

Be careful. This also removes other column definitions like AUTO_INCREMENT. – Colston

This is huge, I'm getting over a 1000 lines of SQL for one of our tables and would have probably lost all my hair loooong before getting anywhere close to migrating these columns myself. Thanks! – Cormorant

Answer 3

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.

Funk

Answer 4

If you can stop the database then try dump tables to text file, change columns definition manually in the file and import tables back.

Observable

Answer 5

Here is my small contribution to this thread too. Thanks to Daniel Schneller and Wiktor Jarka for inspiration and giving me a huge part of the solution! This solution fix database and autoincrement issues.

SET group_concat_max_len = 2048;
SET @database_name = "my_database";
SET @table_name = "my_table";
SET @change = "tinyint 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, ' NOT NULL AUTO_INCREMENT'),
           ';',
           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 `constraint_schema` = @database_name
   AND `referenced_table_name` = @table_name
GROUP BY `referenced_table_name`

Toffey

Answer 6

please don't use answers above

1. group concat is a bad idea as you don't control the length of the output
2. you should use MODIFY and not CHANGE as you don't rename the column
3. other answers don't handle null, defaults, so it could mess your database

my solution, handles nullable, default value, auto increment, and also comments :

SET @table_schema = "database";
SET @table_name = "table";
SET @column_name = "column";
SET @new_type = "bigint unsigned";


SELECT CONCAT('ALTER TABLE `', table_schema,'`.`', table_name, '` DROP FOREIGN KEY `', constraint_name,  '`;' ) AS drop_fk
FROM information_schema.key_column_usage
WHERE referenced_table_schema =  @table_schema AND referenced_table_name = @table_name and referenced_column_name = @column_name;


SELECT CONCAT('ALTER TABLE `', kcu.table_schema,'`.`', kcu.table_name,'` MODIFY `', kcu.column_name, '` ',
@new_type, 
if(c.is_nullable = 'NO', ' NOT NULL', ' NULL'),
if(c.column_default is not null, CONCAT(' DEFAULT ', QUOTE(c.column_default)), ''),
if(c.extra <> '', CONCAT(' ', c.extra), ''),
if(c.column_comment <> '', CONCAT(' COMMENT ', QUOTE(c.column_comment), ''), ''),
';') AS modify
FROM information_schema.key_column_usage AS kcu
JOIN information_schema.columns AS c ON c.table_schema=kcu.table_schema AND c.table_name=kcu.table_name AND c.column_name=kcu.column_name
WHERE kcu.referenced_table_schema = @table_schema AND kcu.referenced_table_name = @table_name AND kcu.referenced_column_name = @column_name;


SELECT CONCAT('ALTER TABLE `', table_schema,'`.`',table_name,'` ADD CONSTRAINT `',constraint_name ,'` FOREIGN KEY (`', column_name, '`) REFERENCES `',
referenced_table_schema ,'`.`', referenced_table_name , '` (`', referenced_column_name,'`);') AS create_fk
FROM information_schema.key_column_usage
WHERE referenced_table_schema = @table_schema AND referenced_table_name = @table_name AND referenced_column_name = @column_name;

Incautious

I forgot that you may have to run an OPTIMIZE TABLE after changing a column type that is indexed. I remember that once a while ago, I changed a type, and the index was not recalculated, leading in bad performance. I dunno if mysql corrected this. – Incautious

See also this answer that recreates foreign key constraints that involve multiple columns: https://mcmap.net/q/669766/-how-to-export-mysql-foreign-keys-only – Carding

Answer 7

You can disable foreign keys temporarily by typing

SET foreign_key_checks = 0;

and to reenable them

SET foreign_key_checks = 1;

I think this requires admin privileges, because it's intended to import data into the database.

Edit: In reaction to your edit, it looks like it only disables constraints for DML statements (insert, update, delete) but not DDL statements (alter table, drop table, etc...).

Lenardlenci

Answer 8

Wiktor's solution with Christophe's additions but also handles ON UPDATE and ON DELETE for the foreign keys

and prints the result in the end

set @change = 'bigint unsigned';
set @targetColumnName = 'id_order';
set @targetTableName = 'orders';

set group_concat_max_len = 8192;
 select distinct kcu.table_schema,
        kcu.table_name,
       kcu.column_name,
       kcu.constraint_name,
       kcu.referenced_table_name,
       referenced_column_name,
       update_rule,
       delete_rule,
       CONCAT(
           GROUP_CONCAT('ALTER TABLE `',kcu.table_name,'` DROP FOREIGN KEY ',kcu.constraint_name SEPARATOR ';\n'),
           ';\n',
           GROUP_CONCAT('ALTER TABLE `',kcu.table_name,'` MODIFY `',kcu.column_name,'` ', @change,
               if(c.is_nullable = 'NO', ' NOT NULL', ' NULL'),
               if(c.column_default is not null, CONCAT(' DEFAULT ', c.column_default), ''),
               if(c.extra <> '', CONCAT(' ', c.extra), ''),
               if(c.column_comment <> '', CONCAT(' COMMENT ', QUOTE(c.column_comment), ''), '')
               SEPARATOR ';\n'),
           ';\n',
           CONCAT('ALTER TABLE `', @targetTableName, '` MODIFY `',@targetColumnName,'` ',@change, ' AUTO_INCREMENT'),
           ';\n',
           GROUP_CONCAT('ALTER TABLE `',kcu.table_name,'` ADD CONSTRAINT `',kcu.constraint_name,'` FOREIGN KEY(',kcu.column_name,') REFERENCES `', @targetTableName, '` (`',@targetColumnName,'`) ON UPDATE ', update_rule, ' ON DELETE ', delete_rule  SEPARATOR ';\n'),
            ';\n'
       ) as query
 into  @table_schema,
       @table_name,
       @column_name,
       @constraint_name,
       @referenced_table_name,
       @referenced_column_name,
       @update_rule,
       @delete_rule,
       @queryString
FROM information_schema.key_column_usage AS kcu
JOIN information_schema.columns AS c ON c.table_schema=kcu.table_schema AND c.table_name=kcu.table_name AND c.column_name=kcu.column_name
INNER JOIN information_schema.REFERENTIAL_CONSTRAINTS AS ref ON
kcu.CONSTRAINT_NAME = ref.CONSTRAINT_NAME
where referenced_column_name = @targetColumnName
   and kcu.referenced_table_name = @targetTableName;

SELECT @queryString;

Fledge