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;

Answer 1

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

Answer 2

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.

Answer 3

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.

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.