Surrogate or natural keys? Preserve identity and business uniqueness
Use a customer email change to test stable references and compare surrogate ids, natural codes, and scoped business uniqueness.
A natural key comes from the domain, such as an ISO country code or an externally assigned invoice number. A surrogate key is created by the system, commonly a numeric id or UUID. Natural keys are convenient when they are genuinely stable and compact. Surrogates are useful when a business value can change, is long, is sensitive, or is not known at creation time.
Often the strongest design uses both: a surrogate primary key for relationships and a UNIQUE constraint on the business identifier. That protects the rule “email is unique” or “external payment reference is unique” without forcing every dependent table to carry that mutable value as its key.
An email changes while an order stays the same
A customer has customer_id 7 and email old@example.test. Two orders reference customer_id 7. When the email changes, the orders should still refer to the same customer. A surrogate primary key separates that stable reference from the mutable business value.
CREATE TABLE customers (
customer_id BIGINT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE
);
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
UPDATE customers SET email = 'new@example.test' WHERE customer_id = 7;The update assumes customer 7 has already been inserted. The surrogate primary key alone does not prevent two customer rows with the same email. The separate UNIQUE constraint preserves that business rule.
Test the scope of uniqueness
Insert two orders for customer 7, change the email, and join orders to customers. Both orders should return id 7 and the new email. Attempt a second customer with that email: it should fail if emails are globally unique. If accounts are unique only within a tenant, use UNIQUE (tenant_id, email) instead and test both a same-tenant duplicate and a different-tenant reuse.
| Candidate key | What can change? | What else is required? |
|---|---|---|
| Owner spelling or account policy | Normalization and uniqueness scope | |
| Generated customer id | Usually independent of business updates | A UNIQUE rule for the business identifier |
| Country code | Rare changes, but not impossible | A policy for retired or replaced codes |
Choose a case and whitespace policy before relying on database collation. Database equality may differ from the equality used by your login service. Imported data must follow the same policy as new registrations.
A generated id does not solve every identity problem
Adding a surrogate attribute does not shorten an identifying chain if the child's primary key still includes its ancestor keys. The physical choice changes only when the child uses an independent primary key and preserves any required parent-scoped uniqueness separately.
Sequential ids and UUIDs both need authorization checks. An unpredictable id is not permission to read the row. In a small reference table, a meaningful stable code can still be the simplest primary key. Judge the choice by change behavior, reference size, and the business rule it must protect.
Reference and next step
Examples use simplified business rules for teaching. Check the database documentation for the exact enforcement behavior of your chosen engine.
Open the ecommerce sample in YourERD · Explore the sample models