How to seek foreign key violations on whole database? (I'm currently using MySQL)

Question

In databases where foreign key checking has been disabled in the past, how can one check for foreign key constraint violations?

Answer 1

there is no built-in way to do this. the only thing i can think of would be to look at the TABLE_CONSTRAINTS and KEY_COLUMN_USAGE tables in the INFORMATION_SCHEMA database to manually check for rows that don't match.

Answer 2

Basically, if you have no foreign key constraints, you can do this:

SELECT * FROM CHILD C WHERE C.PARENT_ID NOT IN (SELECT ID FROM PARENT);