Natural Key

Definition

A natural key is a candidate key made from an attribute (or attributes) that already has real-world, business meaning — an email address, a national ID number, an ISBN, a vehicle registration number — as opposed to a value invented purely for database bookkeeping.

How It Works

Example: a books table might use isbn as its natural key — ISBNs are already globally unique by design (assigned by an external standards body), so the database doesn't need to invent anything; it can simply rely on a value that already carries real meaning to users.

sql
CREATE TABLE books (
  isbn VARCHAR(13) PRIMARY KEY,   -- natural key
  title VARCHAR(200) NOT NULL,
  author VARCHAR(200)
);

The appeal of a natural key is that it's meaningful outside the database too — a person can look at isbn = '9780134685991' and, with an external lookup, know exactly which book that is, without needing to consult this specific database first.

Edge Cases and Pitfalls

• Natural keys can change: a person's email, phone number, or even (rarely) national ID can be reissued or corrected — a primary key value that needs to change later is operationally painful, especially once other tables have foreign keys pointing at it (see 5.3, 5.5).
• Natural keys are sometimes not actually as unique as assumed in practice: two people can share a name, some legacy systems reused ID numbers, and data-entry errors can create accidental duplicates that only surface once the constraint is actually enforced.
• Natural keys can be long, composite, or of an inconvenient data type (e.g. a compound business code) — this can make foreign keys that reference them wider and slower to index than a compact surrogate integer would be.

Key Takeaways

• Natural key = a real-world-meaningful attribute used as a key, as opposed to an artificial one.
• Advantage: no invented value needed, and it's independently meaningful.
• Disadvantage: real-world values can change or turn out to be less unique than assumed — this is exactly the trade-off against a surrogate key (5.8).