Flags Postgres columns that look like foreign keys but have no constraint.

Examples

Before, account_id looks like a relationship but the database does not enforce it:

CREATE TABLE orders (
  id uuid PRIMARY KEY,
  account_id uuid NOT NULL
);

Application code can insert orphaned orders.account_id values, and bugs may only surface later as missing joins or cleanup problems.

After, make the relationship explicit in Postgres:

CREATE TABLE orders (
  id uuid PRIMARY KEY,
  account_id uuid NOT NULL REFERENCES accounts(id)
);

The database now rejects orphaned rows no matter which code path writes to the table.

Notes

This check does not infer relationships from column names that do not end in _id, and it does not validate whether the referenced table name matches the column name. It only checks whether a candidate column is covered by a Postgres foreign key constraint.

Options

• :validate - explicit false disables this check.
• :rules - optional rule keyword list or non-empty list of rule keyword lists. Rules use only shared scope keys.

Run globally with defaults:

Bylaw.Db.Adapters.Postgres.Checks.MissingForeignKeyConstraints

Run only for matching rule scopes:

{Bylaw.Db.Adapters.Postgres.Checks.MissingForeignKeyConstraints,
 rules: [where: [schemas: ["public"]]]}

{Bylaw.Db.Adapters.Postgres.Checks.MissingForeignKeyConstraints,
 rules: [
   where: [schemas: ["public"]],
   except: [
     [tables: ["events"], columns: ["actor_id"]],
     [columns: [~r/_external_id$/]]
   ]
 ]}

A column is treated as a candidate when it ends in _id, is not named id, is not part of a primary key, and is not covered by a declared foreign key constraint.

Usage

Add this module to the checks passed to Bylaw.Db.Adapters.Postgres.validate/2. See the README usage section for the full ExUnit setup.