Remove Foreign Key Constraint Violations

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` 
         ON `table_a`.`entity_id`=`table_b`.`id`
         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.