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

By default the check inspects all non-system schemas in a Postgres target. Use schemas: [...] or tables: [...] for simple filtering:

{Bylaw.Db.Adapters.Postgres.Checks.MissingForeignKeyConstraints,
 schemas: ["public"],
 tables: ["orders", "line_items"]}

Use rules: [...] when the scope needs column matchers or exclusions:

{Bylaw.Db.Adapters.Postgres.Checks.MissingForeignKeyConstraints,
 rules: [
   [
     only: [schema: "public"],
     except: [
       [table: "events", column: "actor_id"],
       [column: ~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.