I had to restore the backup of a table earlier today, and as the application had moved on significantly since then, I was faced with some foreign key constraint violations.
Here's how I resolved them:
table_a is my new, backed up table with incorrect data,
table_b the table that is the object of the foreign key relationship
DELETE FROM `table_a`
WHERE `entity_id` IN (
SELECT * FROM (
SELECT entity_id FROM `table_a` AS `table_a`
LEFT JOIN `table_b` AS `table_b`
WHERE `table_b`.`id` IS NULL
) AS `table`
Running this removed any keys that should no longer exist in the foreign key relationship.
In my case, my outdated table was
product_prices, and the object of the relationship was the
products table. Since some products had been deleted since the last backup, the
product_id in the
product_prices table was giving the foreign key constraint violation. The above script allowed me to remove any
product_prices that were now not required in the table and so, resolve the foreign key constraint violation.