MySQL database keys checks

key-checks.sql

set @table_schema = 'zhf';

-- ### Find missing foreign keys ###
-- select all columns ending with 'Id' (are supoused to be FK) which don't have key starting with 'FK_'
select c.table_schema, c.table_name, c.column_name, c.column_type
from information_schema.columns c
left join information_schema.key_column_usage k on (
  c.table_schema = k.table_schema and
  c.table_name = k.table_name and
  c.column_name = k.column_name and
  k.constraint_name like 'FK_%')
where c.table_schema = @table_schema 
  and c.column_name like '%Id'
  and c.column_name != 'id'
  and k.constraint_name is null;

-- ### Find missing primary keys ###
select t.table_schema, t.table_name, t.table_type
from information_schema.tables t
left join information_schema.key_column_usage k on (
  t.table_schema = k.table_schema and
  t.table_name = k.table_name and
  k.constraint_name like 'PRIMARY')
where t.table_schema = @table_schema
  and k.constraint_name is null;
  
-- ### Find FK with wrong name ###
select *
from information_schema.table_constraints tc
left join information_schema.key_column_usage k on  (
  tc.constraint_name = k.constraint_name )
where tc.table_schema = @table_schema
  and k.table_schema = @table_schema
  and tc.constraint_type = 'FOREIGN KEY'
  and tc.constraint_name != concat('FK_', k.table_name, '_', k.referenced_table_name);