Natural keys

Learn natural keys.

Natural key

A natural key is a value that already exists in the business world: national ID, email, IBAN. It needs no extra column but, if it changes, all references break.

CREATE TABLE countries (
  iso_code CHAR(2) PRIMARY KEY,
  name TEXT
);

A natural key is a business value.

The table countries is created.

Natural key — details

A natural key works well when it is short and immutable. If long or volatile, a surrogate key is preferred. Most designers use a surrogate key with a unique constraint on the natural key.

Exercise Natural key

Create the countries table with iso_code CHAR(2) PRIMARY KEY as natural key and name TEXT.

Show example solution

CREATE TABLE countries (
  iso_code CHAR(2) PRIMARY KEY,
  name TEXT NOT NULL
);

Test yourself

Which of the following is an example of a natural key?
- A random UUID
- An email address
- An auto-increment id
- A counter value
Show answer

An email address — An email address is a business value, a natural key.

Primary key on a natural key

CREATE TABLE countries (____ PRIMARY KEY, name TEXT)

Show answer

CREATE TABLE countries (iso_code CHAR(2) PRIMARY KEY, name TEXT)

Natural keys: match the terms

natural

natural
business

domain
email

email
immutable

stable

Next lesson: Denormalization

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies