# Every table has an explicit primary key

- **Pattern:** `ab-000897` (`database-design-operations.schema-design.primary-keys-defined`)
- **Severity:** critical
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000897
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000897)

## Why it matters

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.

## Severity rationale

Critical because missing PKs break ORM operations, block correct replication, and allow duplicate rows in junction tables — all three failures can corrupt application state silently.

## Remediation

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.

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

```sql
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.

## Detection

- **ID:** `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
  // 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])
  }
  ```

  ```sql
  -- 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)
  );
  ```

---

## External references

- iso-25010 functional-suitability.functional-correctness

Taxons: data-integrity

HTML version: https://auditbuffet.com/patterns/ab-000897