Primary Key

Explanation

Every table should have a primary key — without one, you can't reliably identify a specific row, delete a specific record, or reference it from another table. The PRIMARY KEY constraint enforces uniqueness and NOT NULL, and automatically creates an index on the key column(s). Primary key types: serial/sequence (auto-incrementing integer — simple, compact, ordered, reveals approximate table size and insertion sequence — fine for internal tables), UUID (universally unique identifier — random 128-bit value, globally unique, doesn't reveal sequence — good for publicly-exposed IDs, distributed systems, and security-sensitive contexts where sequential IDs would leak information), composite primary key (two or more columns together form the key — useful for junction tables like user_id + role_id where the combination must be unique). UUIDs vs sequential integers: integers are 4-8 bytes; UUIDs are 16 bytes. Integer primary keys create an ordered B-tree that inserts efficiently (new values go at the end). Random UUIDs create an unordered tree with random insertions, causing page splits and fragmentation (write performance hit for large tables). UUID v7 (sortable UUIDs) addresses this: they're lexicographically sortable while still being globally unique. Natural keys (using a real-world value as the primary key, like email or SSN) are generally discouraged: natural keys can change (users change emails), may not be truly unique, and expose PII in URLs and join conditions. Surrogate keys (generated IDs like serial/UUID) are preferred.

Code Example

-- Primary key options

-- Serial (auto-increment) integer — most common
CREATE TABLE users (
  id    SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL
);
-- Inserts get id = 1, 2, 3, ... automatically

-- UUID — better for public IDs and distributed systems
CREATE TABLE posts (
  id         UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id    INTEGER REFERENCES users(id),
  title      TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Composite primary key — for junction tables
CREATE TABLE user_roles (
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  role_id INTEGER REFERENCES roles(id) ON DELETE CASCADE,
  PRIMARY KEY (user_id, role_id)  -- combination must be unique
);

-- Never use this as a URL param (exposes sequential IDs):
-- GET /users/1, /users/2, /users/3 — tells attackers how many users you have
-- Use UUID in URLs: GET /users/550e8400-e29b-41d4-a716-446655440000

Why It Matters for Engineers

Primary keys are foundational to relational database design. Every JOIN depends on them; every foreign key references them; every ORM uses them. Choosing the wrong primary key type (natural key that changes, sequential ID that leaks information, random UUID that hurts write performance) causes problems that are expensive to fix later. The sequential-ID information leak is a real security issue: if your API returns user ID 42, an attacker knows there are at most ~42 users, and can enumerate by trying /users/1 through /users/42. UUIDs prevent this enumeration attack.