Natural keys
- SQL course
- Expert
- Lesson 4/45
- 5 min
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.
Practice Natural key
Create the countries table with iso_code CHAR(2) PRIMARY KEY as natural key and name TEXT.
Starter code
-- natural key
CREATE TABLE countriesSample solution
CREATE TABLE countries (
iso_code CHAR(2) PRIMARY KEY,
name TEXT NOT NULL
);Expected output
CREATE TABLE
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Which of the following is an example of a natural key?
- A random UUID
- An email address Correct answer
- An auto-increment id
- A counter value
Explanation: An email address is a business value, a natural key.
2 Primary key on a natural key
Question
CREATE TABLE countries (____ PRIMARY KEY, name TEXT)
Answer
CREATE TABLE countries (iso_code CHAR(2) PRIMARY KEY, name TEXT)Explanation: iso_code CHAR(2) is the right answer.
3 Natural keys: match the terms
| Term | Meaning |
|---|---|
natural |
natural |
business |
domain |
email |
|
immutable |
stable |