By coincidence I stumbled upon a table where one of its columns didn’t have a foreign key. I found this strange, since our convention is to always add foreign keys when appropriate.
But humans are humans, and will eventually forget to add a foreign key, so we better have both belt and suspenders and find a way to deal with this inevitable problem in an efficient way.
It would be a tedious job to manually look for missing foreign keys in all tables and columns.
But if you’re lucky enough to have the pleasure of working with a system where all tables and columns have been given their names by following a strict naming convention, it might be possible to fully automate the task.
This is the namning convention we use:
- Table names are always in plural form with a tailing “s”, e.g. users
- Primary key column names are always equal to the table name in singular form with a tailing id, e.g. userid.
- Foreign key columns are always equal to the primary key they are referecning, e.g. transactions.userid -> users.userid
This means you always know based on the column name alone, what table if any that column might be referencing.
Example: If table transactions have a column named userid, and there is a table where userid is also the primary key, but if there isn’t any foreign key on transactions.userid, then it’s a missing foreign key, or else someone has not followed the namning convention.
Thanks to PostgreSQL’s fantastic pg_catalog system tables, we can write a query which uses the rules from the naming convention and returns all the columns which appears to be missing foreign keys. It doesn’t support multi-column keys, but we don’t have many of those, so it’s not a problem in my case.
Thanks to the view below, I automatically found three more missing foreign keys of the same type, which saves me many hours of boring work today.
pg1:joel=#* SELECT * FROM view_missing_foreign_keys; nspname | relname | attname ---------+-----------------+--------- public | transactions | userid public | someohtertable1 | userid public | someothertable2 | userid (5 rows)
I’m posting the view I wrote here in hope it might be useful for others with a similar naming convention, and/or to inspire others to include the table name in their column names used as primary/foreign keys.
CREATE OR REPLACE VIEW view_missing_foreign_keys AS SELECT pg_catalog.pg_namespace.nspname, pg_catalog.pg_class.relname, pg_catalog.pg_attribute.attname FROM pg_catalog.pg_namespace INNER JOIN pg_catalog.pg_class ON (pg_catalog.pg_class.relnamespace = pg_catalog.pg_namespace.oid) INNER JOIN pg_catalog.pg_attribute ON (pg_catalog.pg_attribute.attrelid = pg_catalog.pg_class.oid) WHERE pg_catalog.pg_class.relkind = 'r' AND pg_catalog.pg_attribute.attnum > 0 AND NOT pg_catalog.pg_attribute.attisdropped AND pg_catalog.pg_namespace.nspname NOT IN ('pg_toast','information_schema','pg_catalog') AND pg_catalog.pg_attribute.attname LIKE '%id' AND EXISTS ( -- The column is PRIMARY KEY in some table SELECT 1 FROM pg_catalog.pg_constraint WHERE pg_catalog.pg_constraint.contype = 'p' AND pg_catalog.pg_get_constraintdef(pg_catalog.pg_constraint.oid) = format('PRIMARY KEY (%s)',pg_catalog.pg_attribute.attname) ) AND NOT EXISTS ( -- There is no FOREIGN KEY on this column SELECT 1 FROM pg_catalog.pg_constraint WHERE pg_catalog.pg_constraint.contype = 'f' AND pg_catalog.pg_constraint.conrelid = pg_catalog.pg_class.oid AND pg_catalog.pg_get_constraintdef(pg_catalog.pg_constraint.oid) LIKE (format('FOREIGN KEY (%s)',pg_catalog.pg_attribute.attname) || '%') ) AND NOT EXISTS ( -- This column is not the PRIMARY KEY of it's own table, -- since if it was, we wouldn't require a FOREIGN KEY on it SELECT 1 FROM pg_catalog.pg_constraint WHERE pg_catalog.pg_constraint.contype = 'p' AND pg_catalog.pg_constraint.conrelid = pg_catalog.pg_class.oid AND pg_catalog.pg_get_constraintdef(pg_catalog.pg_constraint.oid) = format('PRIMARY KEY (%s)',pg_catalog.pg_attribute.attname) ) ORDER BY pg_catalog.pg_namespace.nspname, pg_catalog.pg_class.relname, pg_catalog.pg_attribute.attnum