MySQL: Is there a way to update a field so that all foreign key related fields are updated as well

foreign keyMySQLupdate

I'm assuming the answer to this question is yes but it is hard to Google this type of question and not get overly ambiguous results.

I am working with a database that was dumped from a Magento instance – EAV architecture. It stores orders with the column entity_id and uses this field throughout the DB, something close to 20 tables by my count. Magento uses foreign keys on this entity_id column to keep track of its relationship to other tables.

My Question: Is there any way to run an update query so that the MySQL DB will automatically update every foreign key related column in the database? I need to "shift" about 1000 of these incremental entity IDs in order to successful merge orders from another Magento instance. Thank you.

Example of update: Change entity_id from 9567 to 10567.

Best Answer

It is not possible to update the multiple tables interlinked with foreign keys. The constraints in MySQL only updates the related column.

Hope this helps!