Primary Key Coverage Identity

---
name: primary-key-coverage-identity
description: "Primary Key Coverage & Identity Columns"
metadata:
  short-description: "Primary Key Coverage & Identity Columns"
  original-index: 28
---

# Primary Key Coverage & Identity Columns

> Source: [PostgreSQL Best Practices](../DOCUMENTS/PostgreSQL_BestPractices_Azure.md)
> -- "Primary keys: Prefer BIGINT identity or UUID" and Audit Finding #5

## What

Ensuring every table has a primary key, and that PK columns use the modern
`GENERATED BY DEFAULT AS IDENTITY` pattern instead of `serial` + `nextval()`.

## Why

Tables without primary keys are a schema smell:
- No guaranteed row uniqueness -- duplicates can accumulate silently
- `UPDATE` and `DELETE` by natural key are fragile and error-prone
- Logical replication REQUIRES a primary key (or replica identity)
- ORMs (Entity Framework, Prisma) expect a PK for change tracking
- `VACUUM` and `HOT` updates work better with a stable ctid path

## How

### Audit: Find tables without primary keys
```sql
SELECT t.tablename
FROM pg_tables t
LEFT JOIN (
    SELECT tc.table_name
    FROM information_schema.table_constraints tc
    WHERE tc.constraint_type = 'PRIMARY KEY'
      AND tc.table_schema = 'public'
) pk ON pk.table_name = t.tablename
WHERE t.schemaname = 'public'
  AND pk.table_name IS NULL
ORDER BY t.tablename;
```

### Add a PK to an existing table (BIGINT identity)
```sql
-- Step 1: Add the identity column
ALTER TABLE public."EmailConfig"
    ADD COLUMN "Id" bigint GENERATED BY DEFAULT AS IDENTITY;

-- Step 2: Backfill (identity auto-generates for new rows)
-- Existing rows already got values from Step 1

-- Step 3: Add the PK constraint
ALTER TABLE public."EmailConfig"
    ADD CONSTRAINT pk_emailconfig PRIMARY KEY ("Id");
```

### Add a PK using an existing column
```sql
-- If the table already has a candidate key column
ALTER TABLE public."Regions"
    ADD CONSTRAINT pk_regions PRIMARY KEY ("RegionId");
```

### New table template (always include PK)
```sql
CREATE TABLE public."MyNewTable" (
    "Id" bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    "Name" text NOT NULL,
    "CreatedAt" timestamptz NOT NULL DEFAULT now(),
    "UpdatedAt" timestamptz NOT NULL DEFAULT now()
);
```

## BIGINT vs UUID Decision

| Type | Pros | Cons | Use When |
|------|------|------|----------|
| `bigint IDENTITY` | Fast, compact (8 bytes), sequential, index-friendly | Not globally unique | Single-database, sequential access |
| `uuid v4` | Globally unique, no coordination needed | 16 bytes, random = index fragmentation | Distributed systems, external IDs |
| `uuid v7` | Globally unique + time-sortable | 16 bytes, requires extension | Distributed + temporal ordering |

**Default recommendation**: `bigint GENERATED BY DEFAULT AS IDENTITY` unless
there is a specific distributed/external ID requirement.

## GENERATED BY DEFAULT vs GENERATED ALWAYS

| Mode | Explicit INSERT Allowed? | Use When |
|------|--------------------------|----------|
| `BY DEFAULT` | Yes -- app can provide the ID | Backward compat, data migration, testing |
| `ALWAYS` | No (unless `OVERRIDING SYSTEM VALUE`) | Strict auto-generation |

**Default recommendation**: `BY DEFAULT` for backward compatibility with
existing application code. See Skill #17 for serial-to-identity conversion.

## When to Use

- Every table audit that finds missing PKs (Audit Finding #5)
- Every new table definition (always include PK)
- When converting serial columns to identity (Skill #17)

## Where We Used It

- ****: Added PKs to `Hospitals` and `ScribeErrorLog`
- ****: Added PK to `ReportWords` (`ReportId` -> `Id` + identity)
- ****: Added PK to `Regions`
- **scheduling Audit**: Finding #6 flagged 2 serial columns

## Related Skills

- **Skill #17** (Serial to Identity Conversion) -- convert existing serial to identity
- **Skill #27** (Naming Conventions) -- PK naming pattern: `pk_<table>`
- **Skill #09** (Foreign Key Constraints) -- parent table MUST have PK/UNIQUE

## References

- [PostgreSQL Best Practices](../DOCUMENTS/PostgreSQL_BestPractices_Azure.md)
  -- "Schema design and data types" and Audit Finding #5
- [scheduling Audit TDD](../DOCUMENTS/scheduling_DB_Audit_TDD.md)
  -- Finding #6

## Gotchas

- Adding a column to a populated table acquires `AccessExclusiveLock` briefly
- `GENERATED BY DEFAULT AS IDENTITY` creates a sequence tied to the column
  via `pg_depend` -- dropping the column drops the sequence automatically
- If the table already has data, ensure no duplicate values exist in the
  candidate key column before adding a UNIQUE or PK constraint
- ORM scaffolding may need re-running after adding PKs to previously
  keyless tables

---

*Category: DDL | Origin: , , *