Use Postgres to check for missing foreign keys – psql

select Distinct column_name, t1.tablename FROM information_schema.columns, (select * from pg_tables where schemaname='public' AND tablename = 'products') t1 WHERE table_name = 'projects' and column_name like '%_id' and column_name not in (

(SELECT
(SELECT a.attname FROM pg_attribute a WHERE a.attrelid = m.oid AND a.attnum = o.conkey[1] AND a.attisdropped = false) AS source_column
FROM
pg_constraint o LEFT JOIN pg_class c ON c.oid = o.conrelid
LEFT JOIN pg_class f ON f.oid = o.confrelid LEFT JOIN pg_class m ON m.oid = o.conrelid
WHERE
o.contype = 'f' AND o.conrelid IN (SELECT oid FROM pg_class c WHERE c.relkind = 'r') AND m.relname = 'products'))

Leave a Reply

Your email address will not be published.