require-primary-key

What triggers it

A CreateStmt that survives three guards — relation.relpersistence is not t, inhRelations is empty or absent, and tableElts is present — and in which no element carries a primary key. Both placements are checked: a column-level ColumnDef.constraints[].Constraint.contype === "CONSTR_PRIMARY", and a table-level Constraint.contype === "CONSTR_PRIMARY".

What does not

Temporary tables, via the relpersistence === "t" check. Anything with a non-empty inhRelations, which covers both legacy INHERITS (parent) and declarative CREATE TABLE c PARTITION OF p ... — verified, the partition child form does populate inhRelations. CREATE TABLE AS and SELECT INTO, which are different node types. A CreateStmt with no tableElts at all.

Where it applies

Applies to every PostgreSQL version MigrationPilot targets. It works on the SQL text alone — no database connection needed.

The lock, and what it blocks

No lock story. The table does not exist until this statement runs, so its ACCESS EXCLUSIVE lock has no one to block. The rule is about the row-identity consequence: with no primary key the default REPLICA IDENTITY has nothing to point at, so logical replication cannot ship UPDATE or DELETE for the table, and UPDATE/DELETE against duplicate rows is ambiguous.

Why it matters

Tables without a primary key cannot be used with logical replication (pglogical, Citus, etc.), cause issues with ORMs, and make row-level operations inefficient.

Unsafe, and safe

CREATE TABLE events (
  name TEXT,
  data JSONB
);

CREATE TABLE events (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT,
  data JSONB
);

What it assumes

There is no lookahead of any kind. CREATE TABLE t (...); ALTER TABLE t ADD PRIMARY KEY (id); in the same file still produces a violation, because the rule only ever looks at the one statement it was handed. UNLOGGED tables are not exempted — only relpersistence === "t" is — so scratch and staging tables get flagged. A UNIQUE NOT NULL column, which can serve as REPLICA IDENTITY USING INDEX, is not accepted as a substitute. And a table built with LIKE parent INCLUDING ALL is flagged, because a TableLikeClause element is neither a ColumnDef nor a Constraint and the rule sees no key in it.

What the CLI prints

⚠ [MP045] WARNING (line 1)
  CREATE TABLE "events" without a primary key. Tables without a PK cannot use logical replication and make row identification ambiguous.

  Safe alternative:
  -- Add a primary key column:
  -- CREATE TABLE events (
  --   id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  --   ...
  -- );

  Why: Tables without a primary key cannot use logical replication (a requirement for zero-downtime upgrades), make UPDATE/DELETE operations ambiguous, prevent efficient foreign key references, and break many ORMs. Add a primary key to every table.
  Docs: https://migrationpilot.dev/rules/mp045

Generated by running the CLI's own formatter over the flagged example above, so it is the text the tool actually produces. A real run also reports the other rules that fire on the same statement; those blocks are left out here.

Turning it off

For one statement, put a comment on the line before it:

-- migrationpilot-disable MP045
CREATE TABLE events (

For the whole project, in .migrationpilotrc.yml — by name or by id:

rules:
  MP045: false

# or keep it, and downgrade it
rules:
  MP045:
    severity: warning

Try it

Open this rule's flagged example in the playground. It runs in your browser — edit it and watch the finding appear and disappear.

Related rules

• MP038prefer-bigint-over-intuse BIGINT for primary keys
• MP039prefer-identity-over-serialuse IDENTITY for auto-increment PKs
• MP023require-if-not-existsCREATE TABLE should use IF NOT EXISTS