Skip to main content

MY.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: MySQL 8.4 and newer

What it checks​

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

Reported:

Schema::create('moments', function (Blueprint $table) {
    $table->dateTime('at')->primary();   // datetime
});

Schema::create('events', function (Blueprint $table) {
    $table->id();
    $table->timestamp('happened_at')->nullable();     // timestamp -- a different type
    $table->foreign('happened_at')->references('at')->on('moments');
});

Not reported:

Schema::create('moments', function (Blueprint $table) {
    $table->dateTime('at')->primary();   // datetime
});

Schema::create('events', function (Blueprint $table) {
    $table->id();
    $table->dateTime('happened_at')->nullable();      // the same type on both ends
    $table->foreign('happened_at')->references('at')->on('moments');
});

Half of what its sibling reports cannot exist here​

MySQL 8.4.10 refuses an integer-width mismatch outright. Measured:

CREATE TABLE invoices (account_id int, FOREIGN KEY (account_id) REFERENCES accounts (id));
ERROR 3780 (HY000): Referencing column 'account_id' and referenced column 'id'
                    in foreign key constraint 'invoices_ibfk_1' are incompatible.

Signed or unsigned makes no difference — both are refused. So the case that motivates PG.L9.TYPE_IMPLICIT_CAST never reaches a MySQL catalog, and a rule that went looking for it here would be a check that cannot fail.

What DOES survive is the interesting half​

Also measured on 8.4.10, created without complaint:

timestamp  referencing  datetime      -- DIFFERENT types, converted on every comparison
varchar(50) referencing varchar(100)  -- same type, different length: no conversion

The first is what this rule is for: a pair MySQL accepted and converts at runtime, on a join the application runs constantly, with nothing in the schema recording that it was ever intended.

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.

MySQL has no free pair​

PostgreSQL has one — text and varchar share storage. MySQL does not: varchar and char differ in padding, and text differs from both in storage and in how an index over it works. The equivalence list for this engine is therefore empty, and it stays empty until a pair is shown to cost nothing. Every group added to it silences a real finding.

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​

Not transferable to MariaDB. Its foreign-key compatibility rules and its information_schema shapes are its own, and this package targets MySQL 8.4 semantics — advice derived from them and applied elsewhere would be confident, specific and about another product.