Skip to main content

MY.L5.FK_NULLABLE_IN_UNIQUE — A unique key that does not hold, because one of its foreign keys may be NULL

  • Category: idiom
  • Level: 5
  • Confidence: deterministic
  • Downtime class: none
  • Stability: stable
  • Suites: audit
  • 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.

What actually happens​

A UNIQUE KEY (team_id, member_id) reads as one row per pair. With a nullable member_id it is not that.

Measured on MySQL 8.4: three rows sharing the same team_id and carrying a NULL in member_id all insert successfully. The key is enforced for every row that fills both columns, and for none of the rows that do not — which are exactly the rows somebody would want it to catch.

Flagged​

Schema::create('team_member', function (Blueprint $table) {
    $table->foreignId('team_id')->constrained();
    $table->foreignId('member_id')->nullable()->constrained('users');
    $table->unique(['team_id', 'member_id']);
});

Preferred​

Schema::create('team_member', function (Blueprint $table) {
    $table->foreignId('team_id')->constrained();
    $table->foreignId('member_id')->constrained('users');
    $table->unique(['team_id', 'member_id']);
});

MySQL leaves one fewer way out than PostgreSQL​

This is the whole reason the two engines get separate rules over one shared judgment.

PostgreSQL offers UNIQUE … NULLS NOT DISTINCT, which closes the hole while keeping the column optional. MySQL has no counterpart at all. So here the answer is NOT NULL — or, where the column genuinely must stay optional, a stored generated column carrying a sentinel with the unique key over that instead:

ALTER TABLE team_member
  ADD COLUMN member_key bigint unsigned
      GENERATED ALWAYS AS (COALESCE(member_id, 0)) STORED,
  ADD UNIQUE KEY team_member_uq (team_id, member_key);

Naming PostgreSQL's option on this page would be advice that does not compile.

There is a second consequence, and it is a feature rather than a gap: with only one NULL treatment, the question is always decidable on MySQL. This rule never answers undetermined. Its PostgreSQL sister sometimes must.

Why the cut is this narrow​

"A nullable foreign key" on its own is the most false-positive-prone thing this package could report. Three narrowings apply, each a deliberate decision to stay silent about real cases:

Inside a unique key. Outside one, a nullable foreign key constrains nothing and claims nothing.

At least two columns. A single-column nullable unique key is the optional-one-to-one idiom, and it works precisely because NULLs are distinct.

Every member itself a foreign-key column. A key mixing a foreign key with a payload column is the application's own uniqueness rule, and whether a NULL belongs in it is a domain question. A key made entirely of foreign keys is a relationship, and a relationship with a missing side is not one.

What is not reported​

A nullable column inside a primary key. Measured: MySQL rewrites such a column to NOT NULL silently and PostgreSQL refuses it outright, so the state is unreachable from a live catalog. A check for it would be code that cannot execute.

Sources​