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.

One shape is not reported here: a referencing column that is a narrower integer than the key it points at. That one breaks on a date, and PG.L6.FK_NARROWER_THAN_KEY reports it three levels earlier. A baseline entry for this rule on such a key no longer matches; the key is reported under that id now.

Reported:

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

Schema::create('invoices', function (Blueprint $table) {
$table->id();
$table->bigInteger('account_id'); // bigint -- wider 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 the finding says​

PostgreSQL compares the two ends by converting one of them, which the schema did not ask for and nothing records. A key wider than what it references is not going to run out, so the finding promises no date; it asks for both ends to be declared as the same type.

The usual argument against such a pair, that the comparison stops using the index, is false for integers on PostgreSQL: measured on 18.4, the btree integer operator family carries cross-type operators, and EXPLAIN answers with an index scan. The page of PG.L6.FK_NARROWER_THAN_KEY shows the measurement.

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​

Every shape this rule reports gets a considered none: a conclusion, not a gap. The one shape with a standard sequence, the narrowing integer pair, moved to PG.L6.FK_NARROWER_THAN_KEY together with that sequence.

ShapeWhy there is no standard sequence
a composite keyIts columns cannot be changed 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 a payload whose strategy is none: this rule has looked, and there is no safe standard sequence. That is a conclusion rather than an omission — a finding with no payload at all says only that nobody wrote one.

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.