Skip to main content

PG.L5.FK_NO_INDEX — Foreign key whose referencing column no index covers

  • Category: performance
  • Level: 5
  • Confidence: deterministic
  • Downtime class: none — nothing here is a schema change
  • Stability: stable
  • Suites: lint, audit
  • Applies to: PostgreSQL 18

The half of a foreign key PostgreSQL does not index​

Creating a foreign key gives you one index for free, and it is the wrong one. The referenced side is already unique — that is what makes it referenceable. The referencing side, the column in the child table, gets nothing.

That matters on every referential action. Deleting a customer, or changing the key a row is referenced by, forces the server to answer "does any row still point at this?" — and with no index on order_lines.order_id, the only way to answer it is to read all of order_lines. On a table of any size that turns a one-row DELETE into a sequential scan, under a lock, at the exact moment a deploy or a cascading cleanup is running.

PostgreSQL says so in its own manual, and still does not do it for you:

Since a DELETE of a row from the referenced table or an UPDATE of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns too.

Flagged​

Schema::table('order_lines', function (Blueprint $table) {
    $table->foreignId('order_id')->constrained();
});

Preferred​

Schema::table('order_lines', function (Blueprint $table) {
    $table->foreignId('order_id')->constrained();
    $table->index('order_id');
});

constrained() creates the key and no index. One line closes it.

Coverage is a left prefix, not a mention​

A multicolumn B-tree is usable for a lookup that constrains its leading columns. So:

Foreign key onIndex onCovered?
(order_id)(order_id)yes
(order_id)(order_id, created_at)yes — the key is the left prefix
(order_id)(created_at, order_id)no — the index leads with the wrong column
(tenant_id, order_id)(tenant_id)no — the index is narrower than the key

A check that asked merely whether the index mentions the columns would call rows three and four covered. That is the direction that matters: reporting a covered key is loud and gets corrected in a minute, while missing an uncovered one is silent and stays wrong for as long as the table lives.

Indexes that exist but do not count​

A partial index (WHERE archived_at IS NULL), an expression index (lower(email)) and one built with a non-default operator class are all real indexes and all appear in the table's index list. None of them covers the lookup a referential action makes, because that lookup is over every row, on the column's own value, under the default ordering.

SQLens therefore does not count them, and when they are the only indexes present the finding says so explicitly rather than reporting a bare "no index covers this". The two readings send you to different places: one means add an index, the other means the index you are looking at is not the one this needs.

In a lint run it answers about one case only​

Against a live schema the catalog holds every index, so the answer is a fail or a pass. Against a migration it usually is neither: a migration cannot show the absence of an index, because the index may have been created three releases ago in a file the run never read. A fail there would be wrong on most healthy projects and a pass would be wrong on exactly the projects this rule exists for.

So the lint suite reports nothing at all about a foreign key added to a table that already exists. Not a pass, not an undetermined — nothing. The undetermined is what the rule reported at first and it was worse than silence: $table->foreignId(...)->constrained() is among the commonest statements a Laravel migration contains, the message would be identical every time because it says something about the lint suite's reach rather than about your migration, nothing in it can be acted on where it is reported, and under strict_undetermined it would fail the gate of every project that has foreign keys at all.

The one case a migration can settle is the one where it creates the table itself:

Schema::create('orders', function (Blueprint $table) {
    $table->id();
});

Schema::create('order_lines', function (Blueprint $table) {
    $table->id();
    $table->foreignId('order_id')->constrained('orders');   // flagged
});

Nothing earlier can have indexed a table that did not exist yet, so nothing outside this run can have indexed it — and no migration in the run indexes order_id. Add ->index() (or a $table->index('order_id') line) and the finding goes away.

The index may arrive in a later migration. The run is the unit, not the file, and deliberately so: you never edit a migration that has run in production, so the correct place to add a missing foreign-key index — including one sqlens:audit reported to you — is a new migration. That file is part of the same pending set, the run sees it, and the finding does not appear.

// 2026_09_06_000001_create_order_lines.php   ← already deployed, never edited
Schema::create('order_lines', function (Blueprint $table) {
    $table->id();
    $table->foreignId('order_id')->constrained('orders');
});

// 2026_09_20_000002_index_order_lines_order_id.php   ← the fix, and the finding is gone
Schema::table('order_lines', function (Blueprint $table) {
    $table->index('order_id');
});

What counts as an index here is a CREATE INDEX on that table, and the index PostgreSQL builds for a UNIQUE or PRIMARY KEY constraint on it. So the textbook pivot draws no finding: $table->unique(['team_id', 'user_id']) covers the key on team_id, exactly as sqlens:audit reads it off the deployed schema. Swap the two columns and the finding appears, because a B-tree only serves a lookup that constrains its leading columns.

Something it cannot read stops it. If the migration creates an index whose column list this suite cannot read — a partial index, an operator class, an explicit method, a sort option — then it no longer holds the table's complete index history, and it reports nothing about that table rather than guessing. sqlens:audit still answers in full against your schema.

"Creates the table" means every table the statement names, the referenced one included. A foreign key names two tables, and which of them carries the key is not recoverable once the statement has been classified, so a migration that creates only one of the pair is left alone — and for the same reason a covering index on either of the two withholds the finding. That under-reports the new-child-against-an-existing-parent shape, and under-reporting is the direction this rule is allowed to be wrong in: the audit suite still answers it in full against your schema.

Why there is no MySQL twin​

InnoDB creates the index itself, and not merely in the common case. Measured against a real MySQL 8.4.10 server: a composite key with no author-declared index gets one created; an index on (b, a) does not satisfy a key on (a, b), so InnoDB creates its own — it left-prefix-matches exactly as this rule does; an index on (a, x) does satisfy a key on (a), so nothing is added; a prefix index s(10) does not count and a full index is created beside it; the resulting index cannot be dropped afterwards (ERROR 1553); and foreign_key_checks=0 changes none of it.

A MY.L5.FK_NO_INDEX would therefore be a check that cannot fire — and a reader who saw it in the rule list would believe SQLens verifies this on MySQL, when in fact the engine covers it and SQLens verifies nothing. The absence is the honest answer, and a test holds it so it is not "repaired" later.

Sources​