Primary Key Design
Good PKs are: unique, immutable, not null, and compact. Options: auto-increment integer, UUID, natural key (email, SKU). Each has tradeoffs.
MySQL
Intermediate
4 min read
Primary Key Design
Example
-- Auto-increment (simple, fast joins, but not globally unique)
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
-- UUID (globally unique, good for distributed systems)
id CHAR(36) PRIMARY KEY DEFAULT (UUID())
-- Composite natural key (when combination is naturally unique)
PRIMARY KEY (order_id, line_number) Pro Tip
UUIDs are larger than integers and fragment indexes — use sequential UUIDs (UUID v7) in MySQL 8.