Home

How to update a primary key value referenced by child records (foreign keys) in MySQL

Fri, 2011.11.18 - 12:02 — müzso
It's quite easy actually. In MySQL you can disable all foreign key checks by setting the foreign_key_checks variable to zero.

Eg. if you want to update a USER_NAME field in your USERS table and you've tons of references to this table column in other tables (either with or without a foreign key) and you've a proper naming convention in your schema (eg. the name of all colums referring to the USER_NAME column end in the USER_NAME string), then you can easily do this update in all your tables and columns.

Using the following query you can create UPDATE statements for all relevant table+column pairs:
SELECT concat('update `', `TABLE_SCHEMA`, '`.`', `TABLE_NAME`, '` set `', `COLUMN_NAME`, '` = \'newusername\' where `', `COLUMN_NAME`, '` = \'oldusername\';') as stmt
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'myappschema'
AND `COLUMN_NAME` LIKE '%USER_NAME'
ORDER BY 1

Now take this bunch of SQL statements, put SET foreign_key_checks = 0; before them and SET foreign_key_checks = 1; behind them and execute them together.

If you've no easy way to query for the table+column pairs, then you can try to look for foreign key references in information_schema.KEY_COLUMN_USAGE:
SELECT concat('update `', `TABLE_SCHEMA`, '`.`', `TABLE_NAME`, '` set `', `COLUMN_NAME`, '` = \'newusername\' where `', `COLUMN_NAME`, '` = \'oldusername\';') as stmt
FROM `information_schema`.`KEY_COLUMN_USAGE`
WHERE 1 = 1
AND `REFERENCED_TABLE_SCHEMA` = 'myappschema'
AND `REFERENCED_TABLE_NAME` = 'USERS'
AND `REFERENCED_COLUMN_NAME` = 'USER_NAME'
ORDER BY 1