Skip to main content

MY.L2.NO_PRIMARY_KEY — InnoDB table left without a primary key

  • Category: safety
  • Level: 2
  • Confidence: deterministic
  • Downtime class: derived per statement — none for a CREATE TABLE, rewrite for a DROP PRIMARY KEY
  • Stability: stable
  • Suites: lint, 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.

On MySQL a table without a primary key is not a matter of schema taste. Three things break, and each of them breaks at the worst possible moment.

Row-based replication turns pathological. Applying one row change on a replica means locating that row, and without a key that is a full table scan — per row. The replica falls behind under exactly the write volume that made replication worth having.

Every online schema-change tool refuses to run. gh-ost needs a PRIMARY KEY or another unique key to chunk the copy by; pt-online-schema-change exits with NO_PRIMARY_OR_UNIQUE_KEY. So the table that most needs an online migration is the one that cannot have one.

The server may reject the statement outright. sql_require_primary_key exists for this, and a managed provider that switches it on turns a keyless migration into a failed deploy.

Why level 2 and not level 5​

The generic level table places "missing primary key" under schema basics, at level 5. For MySQL this rule sits at level 2, with the blocking-DDL core, because the three consequences above are operational risk rather than style — and a team running a low-downtime gate at levels 0–4 is exactly the audience that must see it. Rule membership is driver-specific by design: the same condition being level 5 generically and level 2 on MySQL is the intended shape, not a contradiction.

Flagged​

Schema::create('page_views', function (Blueprint $table) {
    $table->string('path');
    $table->timestamp('seen_at');
});

Preferred​

Schema::create('page_views', function (Blueprint $table) {
    $table->id();
    $table->string('path');
    $table->timestamp('seen_at');
});

A natural composite key works just as well — $table->primary(['tenant', 'slug']) is folded into the CREATE TABLE and reports nothing.

Dropping a key is a second, more expensive case​

ALTER TABLE … DROP PRIMARY KEY is reported too, and it is the worse of the two: it rebuilds the whole table with ALGORITHM=COPY and leaves the table unkeyed afterwards. Replacing a key is fine — add the new one in the same migration and the rule reports nothing, because the table is keyed by the time the migration ends.

The same rule also reads your live database​

This is the one question SQLens answers from a migration and from the server, under one rule id. The lint suite judges the table being created in front of you; the audit suite walks the schema you already have and asks the same thing of every table in it — including the ones created years ago by migrations this run never reads.

One id, deliberately. A second id for the same condition would mean a project that decided one staging table may stay keyless has to silence it twice, and would only find out it had missed one the next time the other suite ran.

What a migration cannot decide, and the server can​

Two shapes are reported as undetermined in a lint run rather than judged:

  • a table created from another one (CREATE TABLE … LIKE, CREATE TABLE … SELECT), whose keys come from the source and are not in the statement;
  • a table with a UNIQUE key but no PRIMARY KEY. InnoDB promotes the first UNIQUE NOT NULL index to the clustered index, so such a table may be perfectly keyed — or not keyed at all if those columns are nullable, and the statement does not say which.

The audit suite settles the second one, because the catalog knows the nullability. A UNIQUE index over columns that are all NOT NULL draws no finding there; the same index over a nullable column does, and for a reason worth stating plainly: two rows holding NULL in that column are not equal to one another, so the index constrains nothing about them and identifies no row. Note that sql_require_primary_key demands a real primary key either way.

An index the reading cannot reason about — a prefix key, an expression index — does not qualify either. InnoDB will not promote one, so counting it would report a table as keyed that is not.

A deliberately keyless log or staging table is a real case. Handle it the way you handle every intentional exception here — a baseline entry, a config ignore, or a migration annotation — rather than by weakening the rule until it stops seeing the ones that matter.

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.