Skip to main content

PG.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: PostgreSQL 18 and newer

What actually happens​

A unique key over (team_id, member_id) reads as one row per pair. Under PostgreSQL's default NULL treatment it is not that.

Measured on 18.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.

Nothing in the schema shows the hole. A reader sees UNIQUE, and UNIQUE is one of the few words in SQL people believe without checking.

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']);
    $table->index('member_id');
});

If the column genuinely has to stay optional, keep it and close the hole instead:

CREATE UNIQUE INDEX team_member_uq ON team_member (team_id, member_id) NULLS NOT DISTINCT;

NULLS NOT DISTINCT arrived in PostgreSQL 15, and Laravel exposes it as ->nullsNotDistinct(). A key declared that way holds across the NULL rows, so this rule says nothing about it.

Why the cut is this narrow​

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

The column must sit inside a unique key. Outside one, a nullable foreign key constrains nothing and claims nothing — there is no contradiction to report.

The key must have at least two columns. A single-column nullable unique key is the optional-one-to-one idiom — profile_id UNIQUE NULL, superseded_by_id UNIQUE NULL — and it works precisely because NULLs are distinct. Reporting it would be reporting the pattern for being itself.

Every member must itself be a foreign-key column. This is the narrowing that makes the rule shippable. A key mixing a foreign key with a payload column — (user_id, slug) — is the application's own uniqueness rule, and whether a NULL belongs in it is a domain question this rule has no standing to answer. 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: PostgreSQL refuses it outright —

ERROR:  column "a" is in a primary key

— and MySQL rewrites it silently. The state is unreachable from a live catalog, so a check for it would be code that cannot execute.

When the NULL treatment could not be read​

Answered undetermined, with the reason unique_null_treatment_unknown — never pass and never fail. The two shapes are identical in every other respect, so reporting would punish the schema that already applied the fix (the most expensive false positive a rule can produce), and passing would claim a constraint nobody verified.

A note on the category​

Filed as idiom. There is a reasonable argument for safety: what it reports is a constraint that does not constrain, which is closer to a data-integrity defect than to a matter of appetite. It stays idiom because the finding is about a schema shape somebody may have chosen deliberately, so the category decides which band switches it off rather than whether the finding is true.

Sources​

  • PostgreSQL 18: unique indexes — unique indexes treat NULLs as distinct by default, so several rows carrying a NULL in a key column can coexist.
  • PostgreSQL 18: CREATE INDEX — NULLS NOT DISTINCT reverses that; pg_index.indnullsnotdistinct reports which mode is in force, which is the flag this rule reads before it dares conclude.