MySQL find invalid foreign keys

user_715808 0
Viewed 9227

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.

foreign-keys mysql
2 Answers

these other answers are fine for small tables but i think they run in O(n^2) which probably isn't ideal for a large db. Instead i used a left join:

SELECT * FROM children c LEFT JOIN parents p ON p.id=c.parent_id WHERE p.id IS NULL AND c.parent_id IS NOT NULL;

Note you may not need that very last not null condition, i did because i wanted to exclude children that didn't have parents (a valid case in my particular scenario)

user_29637030
Related