Skip to main content

PG.L6.FK_NARROWER_THAN_KEY — A foreign key narrower than the key it references

  • Category: idiom
  • Level: 6
  • Confidence: deterministic
  • Downtime class: none
  • Stability: stable
  • Suites: audit
  • Applies to: PostgreSQL 18 and newer

What it checks​

A foreign key whose referencing column is a narrower integer than the column it references: an integer pointing at a bigint, or a smallint at an integer.

Reported:

Schema::create('accounts', function (Blueprint $table) {
    $table->id();                        // bigint
});

Schema::create('invoices', function (Blueprint $table) {
    $table->id();
    $table->integer('account_id');       // int -- narrower than what it points at
    $table->foreign('account_id')->references('id')->on('accounts');
    $table->index('account_id');
});

Not reported:

Schema::create('accounts', function (Blueprint $table) {
    $table->id();                        // bigint
});

Schema::create('invoices', function (Blueprint $table) {
    $table->id();
    $table->foreignId('account_id')->constrained();   // bigint, derived from the target
    $table->index('account_id');
});

What is wrong, and it is dated​

An int column referencing a bigint key can only ever point at the first 2,147,483,647 of its target's 9,223,372,036,854,775,807 values.

Nothing about the schema says so. Every test passes, because a test database never gets there. Then the parent sequence crosses that number, years after the migration, and every insert into the child fails with an out-of-range error naming a column nobody connects to a foreign key.

That is the failure PG.L6.PK_NOT_BIGINT reports at the primary key, and this rule sits on the same level for that reason. The case here is the quieter one: the parent was given the right width, and only the column pointing at it was not.

The usual reason for this is FALSE on PostgreSQL​

Almost every write-up says a mismatched pair still compares but stops using the index. Measured against PostgreSQL 18.4, over a real table with 50,000 rows and an index on the referencing column:

EXPLAIN SELECT * FROM invoices WHERE account_id = 42::bigint;
  ->  Index Scan using invoices_account_idx on invoices
        Index Cond: (account_id = '42'::bigint)

The btree integer operator family carries cross-type operators, so the index is used. The finding names the date, never the index.

When the parent has nearly filled the column​

This rule reports the pair on its types, however far the parent's sequence has got. PG.L5.FK_NEAR_CEILING reads that position and reports the same column one level earlier once the parent has used half of the column's ceiling, or the share set in sqlens.audit.key_ceiling.min_percent_used.

Where the other mismatches went​

PG.L9.TYPE_IMPLICIT_CAST reports every other pair whose two ends differ: a key wider than what it references, and two types that are not two widths of one number. None of them breaks on a date. Until this rule existed, that level-9 rule reported the narrowing pair as well, so a baseline entry for it on such a key no longer matches; the key is reported under this id now.

Audit only, and that is structural​

A migration adding a foreign key carries the type of neither column. The types live in the CREATE TABLE statements, and the referenced table was almost always created by a migration that is no longer pending. The catalog is the only place both ends exist at once. A key whose other end could not be read is reported as undetermined, never as fine.

The fix material it carries​

A single-column key gets the expand/contract sequence: add a bigint column beside the old one, backfill it from a job, ship a release that writes both and reads the new one, move the constraint, and only then drop the old column. Every step is its own migration.

ALTER TABLE … ALTER COLUMN … TYPE bigint reaches the same end state in one statement, and on a small table it is the better choice by a wide margin. It is named in a precondition rather than emitted, because on PostgreSQL it rewrites every row and holds ACCESS EXCLUSIVE while it does, blocking readers as well as writers, and the size at which that stops being acceptable is a fact about your table that this package cannot read.

A composite key gets a considered none: its columns cannot be widened one at a time without deciding an order, and each ALTER takes its own exclusive lock.

Sources​

The fix material this rule carries​

A finding from this rule carries machine-readable fix material. Which sequence depends on the statement:

  • expand_contract — Add the new shape, move readers and writers across, remove the old one — over three deploys.
  • none — Looked at, and there is no safe standard sequence — a statement, not an absence.

The payload is material for you or an agent to apply. SQLens writes no migration and runs no DDL. See the remediation payload for every field, the placeholder semantics, and the version rules a consumer has to follow.