Skip to main content

PG.L5.NO_PRIMARY_KEY — Table with no primary key and nothing that can stand in for one

  • Category: safety
  • Level: 5
  • Confidence: deterministic
  • Downtime class: none — the finding is about the state a table is in, not about a statement
  • Stability: stable
  • Suites: audit
  • Applies to: PostgreSQL 18

The bill arrives later, and somewhere else​

PostgreSQL lets you create a table with no primary key and never mentions it again. Nothing is slow, nothing errors, and the schema looks fine for as long as nobody asks the database to identify a row.

Logical replication refuses the row. A published table with the default REPLICA IDENTITY and no primary key cannot be replicated for UPDATE or DELETE at all:

ERROR:  cannot update table "import_batches" because it does not have a replica identity
        and publishes updates

Note when that arrives. The publication is created happily; the error comes on the first write to that table — in production, on the one table nobody thought about.

Row-level tooling has nothing to hold on to. Change-data-capture, an audit trail, or any "re-run just this row" recovery needs a stable way to name a row. Without a key the only handle is ctid, and a row's physical location changes on UPDATE and on VACUUM FULL.

Deduplication becomes guesswork. Two rows a human would call the same row are two rows the database is happy to keep forever, and no query can tell you which one to delete.

Flagged​

Schema::create('import_batches', function (Blueprint $table) {
    $table->string('external_ref')->nullable()->unique();
    $table->timestamp('imported_at');
});

This one is flagged even though it has a unique index, and that is the case worth understanding — see below.

Preferred​

Schema::create('import_batches', function (Blueprint $table) {
    $table->id();
    $table->string('external_ref')->nullable()->unique();
    $table->timestamp('imported_at');
});

A UNIQUE index can stand in — under conditions the server sets, not this rule​

REPLICA IDENTITY USING INDEX accepts a unique index in place of a primary key, and SQLens accepts exactly the same ones. The index must be:

RequirementWhy
uniqueotherwise it does not identify a row at all
over NOT NULL columns onlytwo rows holding NULL there are not equal to one another, so the index constrains nothing about them
not partialit identifies rows only inside its predicate, and a row outside it is unreachable
not an expression indexthe value indexed is not the value in the row
validthe corpse a canceled CREATE INDEX CONCURRENTLY leaves behind is ignored by the planner

So a table with UNIQUE (token) where token is NOT NULL draws no finding. The flagged example above has a unique index on a nullable column, which is why it is still reported.

When the nullability of an indexed column could not be read, the finding is undetermined rather than a fail — that fact is what the answer turns on, and guessing it either way would be a verdict about something nobody looked at.

What it does not report​

  • Partitions. A partitioned table is judged once, at the parent. The key is a property of the partitioned table, and reporting each partition would multiply one problem by however many partitions the table happens to have.
  • Extension-owned tables. PostGIS alone installs several, and their design is not yours.
  • Views and materialized views. They have no rows of their own to identify.

Why there is no built-in list of table names to skip​

The obvious suggestion is a default ignore list for framework tables — migrations, jobs, cache, sessions. SQLens ships none, and the reason is a measurement rather than a preference: every table Laravel's own stubs create already has a primary key. cache and cache_locks key on key, job_batches and notifications on id, sessions on id, and the migration repository builds its table with increments('id').

A default ignore list would therefore never prevent a false positive — those tables do not trigger the rule — and could only hide a real one, on the day somebody hand-rolls a cache table without a key.

A deliberately keyless table is still a real case: an append-only log, a staging table nothing updates. That belongs in the ignore config with the reason written down, which is how every intentional exception is handled here.

MySQL asks the same question at level 2​

The MySQL twin is MY.L2.NO_PRIMARY_KEY, and it sits four levels lower on purpose. There the same missing key breaks every online schema-change tool and can be rejected outright by sql_require_primary_key — deploy-blocking risk, which belongs in the band a low-downtime gate watches. On PostgreSQL the consequences are just as real but arrive later and elsewhere: in replication and in row-level tooling, not in the migration in front of you.

Sources​