Natural Primary Keys Misuse
Using mutable or sensitive business values like email or SSN as primary keys makes a real-world change cascade through every foreign key and spreads PII. Use stable surrogate keys as the identity and keep natural values as unique-constrained columns.
A natural key is a primary key drawn from real-world business data — an email address, a national ID, a product SKU, a phone number. Natural keys are valuable as unique constraints, but using a mutable or sensitive natural value as the primary key, and propagating it as a foreign key, is an anti-pattern. The key inherits all the instability and sensitivity of the business data.
Why It Happens
The natural value already uniquely identifies the entity, so adding a separate surrogate key feels redundant. It seems convenient that the key is human-readable. Early data models adopt the natural key before anyone considers what happens when that value changes, and by then it is referenced everywhere.
Why It Hurts
Business values change: people change emails, SKUs get reformatted, national ID schemes get reissued. When a primary key changes, every foreign key referencing it must change too, a costly, error-prone cascade that ON UPDATE CASCADE only partly tames and that breaks external references and caches. Sensitive natural keys such as SSNs spread personally identifiable information into every child table and index, worsening privacy exposure. Composite natural keys make joins verbose and indexes wide. Merges and deduplication become painful when the identity itself is the business value.
Warning Signs
- Primary keys are email addresses, usernames, or other editable values.
- Sensitive identifiers like SSNs appear as keys across many tables.
- A correction to a business value requires updating many related rows.
- Composite business keys propagate through every foreign key and join.
Better Alternatives
Use a stable, meaningless surrogate key as the primary key — an auto-increment identity or a UUID — so the identity never has to change. Keep the natural value as a separate column with a unique constraint to enforce business uniqueness without making it the identity. This separates two concerns: how rows are uniquely referenced internally and how they are uniquely identified in the business domain.
How to Refactor Out of It
Add a surrogate primary key to the affected table and populate it. Add a unique constraint on the former natural key to preserve its guarantee. Migrate foreign keys in referencing tables to point at the surrogate, backfilling from the natural value, then drop the old foreign key columns. Update application code and external integrations to use the surrogate for references. The natural value remains queryable and unique but is no longer load-bearing as identity, so future changes to it are harmless.