Skip to main content

MY.L2.FK_TARGET_NON_UNIQUE — Foreign key onto a target no unique key covers

  • Category: safety
  • Level: 2
  • Confidence: deterministic
  • Downtime class: derived from the online-DDL matrix — which withholds it here, see below
  • Stability: stable
  • Suites: lint
  • Applies to: MySQL 8.4
  • Not transferable to MariaDB: this page describes MySQL 8.4 behavior. MariaDB answers the same driver and does not share these semantics, so SQLens refuses it outright rather than reasoning about it — see drivers/unsupported.

This is not a deprecation warning — on MySQL 8.4 the statement fails​

restrict_fk_on_non_standard_key is ON by default in MySQL 8.4, and the server rejects a foreign key whose referenced columns are not covered by a unique key of the target table:

ERROR 6125 (HY000): Failed to add the foreign key constraint. Missing unique key …

Measured against a real MySQL 8.4.10 rather than read out of a release note:

The foreign key references…MySQL 8.4, default configuration
the target's PRIMARY KEYaccepted
a column carrying a non-unique indexERROR 6125
the first column of a composite PRIMARY KEYERROR 6125
a non-first column of a composite PRIMARY KEYERROR 6125
a column with no index at allERROR 6125

So this is a deploy that breaks today, on the version this package supports, in the configuration a user gets without doing anything.

The third row is the one that surprises people: a prefix of a unique key is not a unique key. Referencing the first column of a two-column primary key is rejected exactly as a non-unique index is, which is why "covered" here means the unique key's columns equal the referenced ones.

Flagged​

Schema::create('tenants', function (Blueprint $table) {
    $table->id();
    $table->string('slug');
    $table->index('slug');
});
Schema::table('sites', fn (Blueprint $table) => $table->foreign('tenant_slug')->references('slug')->on('tenants'));

Preferred​

Schema::create('tenants', function (Blueprint $table) {
    $table->id();
    $table->string('slug');
    $table->unique('slug');
});
Schema::table('sites', fn (Blueprint $table) => $table->foreign('tenant_slug')->references('slug')->on('tenants'));

One word — index becomes unique — and the difference is a migration that runs versus one that errors.

When the target table is not in the migration​

A foreign key usually points at a table that already exists, whose indexes live on the server. The lint suite reads no server, so SQLens reports undetermined there rather than guessing in either direction: the ordinary case (referencing a primary key) is fine and flagging it would be crying wolf, but "I did not look" is not "it is fine" either. Confirm that the target carries a unique key on exactly the referenced columns — the audit suite, which does read the live catalog, settles it for you.

Why the finding carries no downtime class​

Adding a foreign key is a conditional entry in the online-DDL matrix: it copies the table under a shared lock unless foreign_key_checks is off, and whether it is off is a session fact no static reader can see. SQLens asks the matrix and the matrix declines, so the finding carries no downtime class rather than the one that happens to be likely.

That is deliberate. Naming rewrite here would be a cost invented in the rule instead of derived from the data — and it would state a cost for a statement MySQL 8.4 is going to reject anyway.

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.