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.

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​