MySQL find invalid foreign keys

Question

We have a database with a couple hundred tables. Tables using foreign_keys use INNODB.

Sometimes we transfer data (individual tables using mysqldump) between our development, stage, and production databases. mysqldump disables all foreign key checking to make importing the data easy.

So over time some of our non-production databases ends up with a few orphaned records.

I was about to write a script that would find and detect any invalid (keys pointing to missing records) foreign keys for an entire MySQL database.

I know I can write a query to check each table and fkey one by one, but was thinking there may be a tool to do this already.

I would check before writing such a script to see if there is one out there already.

Searched google a bit... surprisingly I found nothing.

Answer 1

If the data is already in and you haven't set up fk constraints or cascades for deleting the parent then you just want:

SELECT * FROM children WHERE my_fk_id NOT IN (select id from parents);