Every table has an explicit primary key

Tables without explicit primary keys are unpredictable to query, replicate, and join. Without a declared PK, Postgres cannot enforce entity uniqueness at the database level, ORMs like Prisma refuse to generate correct update and delete operations, and replication tools (Supabase logical replication, AWS DMS) silently skip or mis-replicate affected rows. Junction tables without composite PKs allow duplicate relationship rows that violate relational integrity. The ISO 25010 functional-correctness requirement is unambiguous: every entity must have a stable, unique identifier declared in the schema — not inferred from an implicit rowid that disappears the moment you add a real column.

Add an explicit @id (Prisma) or PRIMARY KEY (SQL) declaration to every table that lacks one. For entity tables, use a UUID or serial surrogate. For junction tables, use a composite PK over both FK columns.

model PostTag {
  postId String
  tagId  String
  @@id([postId, tagId])
}

CREATE TABLE audit_logs (
  id         BIGSERIAL PRIMARY KEY,
  user_id    UUID NOT NULL,
  action     TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

After adding PKs to existing tables, run VACUUM ANALYZE to refresh planner statistics.

• ID: database-design-operations.schema-design.primary-keys-defined

• Severity: critical

• What to look for: Count every model/table definition in the schema — enumerate each one and classify it as "has explicit PK" or "missing PK." In Prisma, look for @id on a single field or @@id([...]) for composite PKs. In raw SQL migration files, look for PRIMARY KEY constraints. In Drizzle, look for .primaryKey() on a column definition. In TypeORM entities, look for @PrimaryColumn() or @PrimaryGeneratedColumn(). In Sequelize, check model definitions for primaryKey: true. Every table and model — including join tables and junction tables — must have an explicit PK. Implicit rowid-based identity (common in SQLite) does not count as an explicit PK.

• Pass criteria: All tables and models have at least 1 explicit primary key declaration each. Every Prisma model has @id or @@id. Every SQL table has a PRIMARY KEY constraint. Join/junction tables use composite PKs or a surrogate UUID/serial PK. Report even on pass: "X of Y tables have explicit PKs."

• Do NOT pass when: Tables rely on SQLite implicit rowid or ORM auto-generated IDs that are not declared in the schema definition. A table having data does not mean it has a declared PK.

• Fail criteria: Any table or model lacks an explicit PK declaration. Relying on SQLite's implicit rowid. Prisma model missing @id. SQL table created without PRIMARY KEY constraint.

• Skip (N/A) when: N/A — always applicable for any project with a database.

• Cross-reference: For data integrity beyond PKs, the Data Protection audit covers encryption-at-rest and access control for sensitive columns.

• Detail on fail: Specify which tables are missing PKs. Example: "Table 'post_tags' (junction table) has no primary key defined in prisma/schema.prisma." or "Migration 003 creates 'audit_logs' table without PRIMARY KEY constraint." or "3 of 12 Prisma models missing @id field.".

• Remediation: Add explicit primary keys to all tables:

  // Prisma — single field PK (surrogate UUID)
  model Post {
    id        String   @id @default(uuid())
    title     String
    // ...
  }
  
  // Prisma — composite PK for junction tables
  model PostTag {
    postId String
    tagId  String
    @@id([postId, tagId])
  }
  

  -- Raw SQL — surrogate serial PK
  CREATE TABLE audit_logs (
    id         BIGSERIAL PRIMARY KEY,
    user_id    UUID NOT NULL,
    action     TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
  );
  
  -- Raw SQL — composite PK
  CREATE TABLE post_tags (
    post_id UUID NOT NULL,
    tag_id  UUID NOT NULL,
    PRIMARY KEY (post_id, tag_id)
  );