Skip to main content

MY.L8.FK_ID_SUFFIX — A foreign key column Laravel cannot derive a relation from

  • Category: convention
  • Level: 8
  • Confidence: deterministic
  • Downtime class: none
  • Stability: stable
  • Suites: lint, audit
  • Applies to: MySQL 8.4 and newer

What it checks​

A foreign key over one column whose name does not end in _id.

Reported:

Schema::create('posts', function (Blueprint $table) {
    $table->id();
    $table->foreignId('author')->constrained('users');
});

Not reported:

Schema::create('posts', function (Blueprint $table) {
    $table->id();
    $table->foreignId('author_id')->constrained('users');
});

Why this one is not taste​

Level 8 is where conventions live, and most of what belongs there is preference. This one buys something specific: Eloquent derives a relation's foreign key from the related model, so belongsTo(User::class) resolves to user_id with nothing written down. A column that departs from it keeps working — and every relation on that side has to pass the key explicitly, forever, in code nobody re-reads when the schema moves.

That is the cost the rule names. Not a defect: a standing tax, paid at each call site, by whoever comes next.

The column is identified by its CONSTRAINT, never by its name​

This is the part that decides whether the rule is worth having.

A rule that decided "this column carries a relation" by seeing _id at the end would find exactly the columns that already satisfy it, and would be blind to author, owner and parent — the ones it exists for. It would pass on every schema, including the ones it was written to fix.

So both halves ask the schema instead. The audit half reads the foreign keys the catalog reports; the lint half judges a statement only when the parse gives it a referenced target, which is what a REFERENCES clause produces and what nothing else does.

On MySQL the canonicalization classifies ALTER TABLE … ADD CONSTRAINT … REFERENCES … as add_foreign_key, while PostgreSQL classifies the same statement as add_constraint. The referenced target is what both engines agree on, which is why it and not the statement kind is what this rule reads — the sister rule on the other engine reads it the same way.

A composite foreign key is not reported​

A multi-column key references a compound key on the other side, and the parts of a compound key carry their own meaning: (country_code, area_code) is a reference whose columns are named after what they hold. _id is a convention about a surrogate key — one column standing in for a row — so demanding it of every member of a natural-key reference would report a false positive on exactly the schemas that modeled their keys most carefully.

Skipped on purpose, and said here so that a composite key going unreported is not read as an oversight.

Two cases that need no configuration​

  • Self-referential. parent_id pointing at its own table ends in _id and passes on its own merits. There is nothing to special-case.
  • Polymorphic. commentable_id ends in _id and passes. Its partner commentable_type carries no foreign key at all — it cannot, since it names a class rather than a row — so it is invisible to a rule that reads constraints.

Configuring it​

// config/sqlens.php
'audit' => [
    'naming' => [
        'foreign_key_suffix' => '_id',   // the default
        'exempt' => ['/^legacy_/'],      // names this rule does not judge at all
    ],
],

An empty suffix is refused and the default is used instead. Every name ends with the empty string, so an empty setting would silence the rule completely — and a project would read "no findings" as "my keys are named the way I asked", when in fact it had asked for nothing.

Suppressing it​

The ordinary routes apply: a baseline entry, an ignore rule in config/sqlens.php, or lowering the level below 8. A project that has chosen another convention should set the suffix rather than suppress the rule, so the rule keeps working for the columns that do not follow it either.

Sources​

  • Eloquent relationships — the framework derives the foreign key from the relationship method name and requires it to be passed explicitly when the column does not match
  • MySQL 8.4 — FOREIGN KEY constraints — what declares a column to be a foreign key, which is what this rule reads instead of the name

Not transferable to MariaDB. Its foreign-key handling 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.