Avoiding Natural Keys as Foreign Keys

1. What makes a natural key look attractive as a foreign key target at first

A natural key is a value with its own business meaning that already has to be unique for application logic reasons, such as an email address for user accounts, a product code in a catalog, or an ISBN for books. Because this value is maintained as unique anyway, it seems natural to use it directly as the primary key and let other tables reference it directly through that business value, instead of introducing an extra, purely technical ID column.

At first glance this saves a column and makes foreign key values immediately readable in query results and debugging sessions, without needing an extra join to resolve them. This apparent advantage only holds, however, as long as the business value itself never changes, an assumption that regularly does not hold in practice.

2. The core problem: business values change, technical identity shouldn't

An email address changes on marriage, a domain switch, or simply at the customer's request. A product code gets reassigned during a numbering system overhaul, a company merger with overlapping number ranges, or a catalog cleanup. An ISBN can change for a new edition of the same underlying work. In all these cases the referenced entity stays the same, only its business identifier changes.

If that business identifier is also the primary key that other tables reference as a foreign key, every such change forces the value to be updated consistently at every referencing point in the schema. What is a single, local business change becomes, technically, a cascade of updates across potentially many tables.

3. How a surrogate key decouples technical identity from business values

A surrogate key is a purely technical, meaningless identifier, typically an auto-increment integer or a UUID, whose only job is uniquely identifying a row and which is never interpreted at the business level. Referencing tables point at this stable, technical value, while the business value, such as the email address, is maintained as an ordinary, changeable attribute in the same row, additionally protected by its own unique constraint against duplicates.

When the business value changes, that only touches a single UPDATE on exactly that one attribute column in exactly that one row. No foreign key in any other table needs to be touched, because no foreign key ever referenced the business value, only the stable, unchanged surrogate key.

-- Surrogate key as the stable reference point,
-- email as an ordinary, changeable attribute
CREATE TABLE app_user (
    id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
    id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES app_user(id)
);

-- Changing the email touches not a single foreign key
UPDATE app_user SET email = 'new-address@example.com' WHERE id = 42;

4. A practical example: a painful migration caused by changed product codes

A product catalog initially uses the product code directly as the primary key, and a dozen other tables, including order lines, stock levels, price histories, and external interface mappings, reference that product code as a foreign key. After a catalog cleanup, several hundred products need new, more consistent codes, partly because old and new number ranges overlap following the acquisition of another manufacturer.

Because the product code is also the primary key, it cannot simply be changed via UPDATE without first updating every referencing foreign key relationship within exactly the same transaction, in many database systems additionally requiring foreign key constraints to be temporarily disabled or marked DEFERRED. On tables with millions of rows and several involved referencing tables, what should be a simple business renumbering turns into a multi-hour, high-risk migration project with locking across a broad set of tables.

5. The same renumbering with a surrogate key as the primary key

Had a surrogate key been used as the primary key from the start, with the product code carried only as a regular, unique-constrained attribute, the same renumbering of several hundred products would have consisted of a series of simple UPDATE statements on the product code column, without touching a single foreign key relationship in any referencing table.

The difference between a risky, multi-hour migration with locking windows and an unremarkable change executable during normal operation lies entirely in this one schema decision, made long before the concrete renumbering was even foreseeable.

-- With a surrogate key: renumbering is a simple,
-- low-risk update of a regular attribute
UPDATE product SET product_code = 'NEW-4471'
    WHERE id = 981234;
-- Not a single foreign key relationship is affected,
-- because every referencing table points at product.id

6. Further risks of natural keys beyond mere changeability

Beyond changeability itself, natural keys as primary keys carry other structural risks. Text-based natural keys like email addresses or product codes are almost always wider than a compact integer, unnecessarily enlarging every foreign key and secondary index referencing that key. Case-sensitivity questions, leading or trailing whitespace, and differing normalization rules across application layers can also produce subtle duplicates that simply cannot arise with a purely technical surrogate key.

It is also worth noting that some seemingly stable natural keys turn out, on closer inspection, to be less unique than assumed. An ISBN, for instance, can vary for the same underlying edition of a book across different regions or formats, undermining the assumed one-to-one relationship between business key and entity from the outset.

7. When a natural key as a primary key is still defensible

Not every natural key is inherently problematic. For very small, practically static reference tables whose values are stable by definition, such as ISO country codes or ISO 4217 currency codes, the readability advantage outweighs the theoretical change effort, because a change to these codes practically never happens in practice and, even then, could be carried out in a controlled way across a manageable number of reference points.

The decisive difference from application data like email addresses or product codes is that these reference values are not altered by end users or business processes but are defined by external, extremely rarely updated standards. For practically every other case involving values controlled by the business or by customers, a surrogate key remains the more robust default decision.

8. The business value still matters, just not as the primary key

Giving up a natural key as a primary key does not mean giving up the business uniqueness check. A unique constraint on the business column still ensures that no two users get the same email address and no two products get the same product code, entirely independent of whether that value is the primary key or not.

Application code that needs to look up a user by their email address, such as during login, still uses a perfectly normal indexed query on that column. The difference is solely that no other table references that business value as a foreign key target, and instead consistently references the stable surrogate key.

-- Business uniqueness stays enforced,
-- without email having to be the primary key
CREATE UNIQUE INDEX idx_app_user_email ON app_user (email);

SELECT id FROM app_user WHERE email = 'customer@example.com';

9. A practical decision rule for schema design

The key test question during schema design is not whether a value is currently unique, but whether it could change at the business level within a foreseeable time frame, and whether that change could be triggered by end users, business processes, or external partners. As soon as that question can even potentially be answered yes, the value belongs in the schema as a regular, unique-constrained attribute, referenced exclusively through a stable surrogate key.

Making this decision early costs only one extra integer or UUID column at the first draft. Fixing it later, as the product code migration example shows, can cost days of risky, production-critical migration work. That imbalance is what makes surrogate keys the default choice for primary keys in practically every new schema.

10. Summary

11. FAQ: Natural Keys as a Foreign Key Trap