Primary Keys

Introduction

A primary key uniquely identifies each row in a table. Every well-designed table has exactly one primary key. It is the anchor that foreign keys point to and the default index for fast lookups.

Understanding the topic

Core concepts to understand:

• Always NOT NULL and UNIQUE — guaranteed by the DB.
• Common types: SERIAL/BIGSERIAL, UUID, composite of two columns.
• Surrogate (id) > natural (email) for stability.
• Generates a clustered/B-tree index automatically.
• Used by foreign keys to link tables.

Syntax reference

Visual workflow / architecture:

users
┌────┬──────────────┐
│ id │ email        │ ← id = PK
└─┬──┴──────────────┘
  │ referenced by
  ▼
orders
┌────┬─────────┬────────┐
│ id │ user_id │ total  │ ← user_id = FK → users.id
└────┴─────────┴────────┘

Real-world use

Every row in every table at GitHub, Stripe and Shopify has a stable primary key. Without it, joining and updating data reliably is impossible.

Best practices

• Always define a PK — never leave it out.
• Prefer BIGINT or UUID for tables that may grow large.
• Don't change PK values once set.

Common mistakes

• Using email or username as PK — they change.
• Composite PKs that are too wide → slow joins.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Properties of a primary key.
• Q2. UUID vs BIGSERIAL pros/cons.
• Q3. Can a table have multiple PKs?
• Q4. Why are natural keys risky?
• Q5. What index does a PK create?