Find missing Foreign Key constraints

README.md

Find missing Foreign Key constraints in a MySQL database

This sql script can help identify FK columns in databases that are missing a constraint.

identify.sql

SELECT c.TABLE_NAME, c.COLUMN_NAME, c.IS_NULLABLE, c.DATA_TYPE, c.COLUMN_TYPE, kcu.* FROM information_schema.COLUMNS c
LEFT OUTER JOIN information_schema.KEY_COLUMN_USAGE kcu ON c.TABLE_SCHEMA = kcu.CONSTRAINT_SCHEMA AND c.TABLE_NAME = kcu.TABLE_NAME AND c.COLUMN_NAME = kcu.COLUMN_NAME
WHERE 
  c.TABLE_SCHEMA = '{DB_TABLE}' AND 
  RIGHT(c.COLUMN_NAME, 3) = '_id' AND 
  kcu.REFERENCED_TABLE_NAME IS NULL 

Simply replace {DB_TABLE} with your database name.