Skip to main content

PG.L9.TYPE_IMPLICIT_CAST — A foreign key whose two ends are different types

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

What it checks​

A foreign key whose referencing column and referenced column are not the same base type.

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');
});

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
});

The usual reason for this rule is FALSE on PostgreSQL​

Almost every write-up of this 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. A rule built on the index argument would ship advice that is confident, specific and wrong — which this package treats as worse than no advice at all.

What is actually wrong is worse, 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 what the finding says, and why it names the number.

A declared length is not a mismatch​

varchar(50) referencing varchar(100) is created by both engines, costs nothing, and is the most common shape in a real schema. The comparison is the base type, so it is silent — a rule comparing declared types would fire on almost every text key it saw.

text and varchar are the same thing here​

PostgreSQL stores them identically — pg_type reports the same typlen, typalign and typstorage for both — so a foreign key across them costs nothing and is silent.

bpchar (char(n)) is deliberately not in that group. It pads to its declared length, so comparing it against either of the others changes answers as well as costing a conversion.

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 when this one runs. The catalog is the only place both ends exist at once.

A lint half would therefore answer undetermined for nearly every foreign key — noise rather than honesty. What the rule does refuse to do is pass silently: a key whose far end could not be read is reported as undetermined, never as agreement.

The fix material it carries, and the one direction that has any​

A finding from this rule carries machine-readable fix material for one of the three shapes it reports: a single-column key whose referencing side is a narrower integer than the key it points at. That one has an end state nobody has to decide — the child must be as wide as what it references — and a deadline, which is what makes a template worth having.

The sequence is expand/contract: 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, not only writers — and the size at which that stops being acceptable is a fact about your table that this package cannot read.

The other two shapes get a considered none: a conclusion, not a gap.

ShapeWhy there is no standard sequence
a composite keyIts columns cannot be widened one at a time without deciding an order, and each ALTER takes its own exclusive lock — including on the columns that were already right. Which goes first depends on what reads the table while it happens.
the referenced key is the narrower sideMatching them means widening the parent. Narrowing the child to fit would cement the smaller ceiling and fail on the first value above it.
the two sides are not two widths of one numberA timestamp against a date, a varchar against a char: the right answer depends on which side is telling the truth about the data, which is a question about your application.

Suppressing it​

Level 9 is the strictest band and is not reached unless a project asks for it. Within it, the ordinary routes apply: a baseline entry, an ignore rule in config/sqlens.php, or lowering the level below 9.

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.