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.

Nitpicking

What do you mean, 'invalid'? Orphaned records? tables with FKs that point to tables that don't exist anymore? – Stamford

You could have your database automatically do this (depending on what DB engine you are using) and have it ON DELETE CASCADE so that referential integrity is upheld. – Darvon

In addition to stefans comment be aware that you can nullify the child or update it, you don't have to delete it. – Thalassa

Yes, by invalid I do mean orphaned records. I will update my question to be more concise. – Nitpicking

Yes, I am aware of ON "DELETE/UPDATE" CASCADE. However, there is already orphaned records in the database. ON "DELETE/UPDATE" CASCADE will only handle future updates and deletes. I will update my question regarding this. – Nitpicking

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);

Thalassa

SELECT t1.* FROM table1 t1 LEFT JOIN table2 t2 ON t1.parent_id = t2.id WHERE t2.id IS NULL – Prosaic

@Isotope The data is already in, but we already have the fk constraints in place. – Nitpicking

This is great for finding invalid keys on a table. However, I am looking for a tool to find these for all tables in the entire database. – Nitpicking

There's no tool to do such a thing because it's such an easy custom job. What do you want to do with all the orphans? Delete them? Using your favourite scripting language just have it iterate through the tables. – Thalassa

Answer 2

WITH RECURSIVE foreigners as 
    (
    SELECT 
    CONSTRAINT_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = '<---->' and TABLE_NAME LIKE '<---->'
    )
SELECT 
   CONCAT('SELECT \'',TABLE_NAME,'.',COLUMN_NAME, '\' as broke, ', COLUMN_NAME,' FROM ',TABLE_NAME,' WHERE ',COLUMN_NAME, ' NOT IN (',' SELECT ',REFERENCED_COLUMN_NAME,' FROM ', REFERENCED_TABLE_NAME,') UNION' ) as x 
    from foreigners
    UNION SELECT 'SELECT null, null'

Intromit

Consider including an explanation with your SQL command. – Scoria

Answer 3

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)

Igal