Part 34: Primary Keys: Natural vs Surrogate Keys & UUIDs

A Primary Key uniquely identifies each row in a table.

1. Natural vs Surrogate Keys

• Natural Key: A real-world attribute that is naturally unique (e.g., Social Security Number, VIN, ISBN).
• Problem: Real-world identifiers change (companies rebrand emails, government systems change numbering).
• Surrogate Key: An artificial, meaningless unique identifier created solely for database management (e.g. auto-incrementing ID or UUID).

2. Auto-Incrementing Integer vs. UUID

Modern Recommended Hybrid Pattern:

Use an internal BIGINT clustered primary key for join performance, and an exposed UUID for external API routes:

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,           -- Internal fast join key
  public_id UUID DEFAULT gen_random_uuid() UNIQUE, -- Exposed in API /order/uuid
  total DECIMAL(10, 2) NOT NULL
);

Key Takeaways

• Prefer surrogate keys over natural keys for business entities.
• Use internal BIGINT keys for fast joins and indexed B-Trees, with exposed UUIDs for public APIs.

In Part 35, we explore Foreign Keys: Referential Integrity & Cascade Actions!