CODEWITHSUNDEEP
mysqlforeign-keys
Michael
Michael

Reputation: 3628

MySQL find invalid foreign keys

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.

Upvotes: 22

Views: 10651

Answers (3)

jj gleeson
jj gleeson

Reputation: 29

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'

Upvotes: 2

stackPusher
stackPusher

Reputation: 6512

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)

Upvotes: 1

TomDunning
TomDunning

Reputation: 4877

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

Upvotes: 25

Related Questions