Surrogate keys
- SQL course
- Expert
- Lesson 3/45
- 5 min
Learn auto-incrementing keys.
Surrogate key
A surrogate key is an artificial key with no business meaning, used only for identification. It auto-increments via SERIAL in PostgreSQL or AUTO_INCREMENT in MySQL.
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT
);SERIAL generates an auto-incrementing integer.
The table products is created.
Surrogate key — details
With a surrogate key, the row identity stays stable even when a natural key changes. This protects foreign key references and eases migration.
Practice Surrogate key
Create the products table with id SERIAL PRIMARY KEY and name TEXT.
Starter code
-- surrogate key
CREATE TABLE productsSample solution
CREATE TABLE products (
id SERIAL 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 Surrogate keys: match the terms
| Term | Meaning |
|---|---|
surrogate |
artificial |
SERIAL |
auto |
business |
domain |
stable |
stable |
2 Auto-incrementing key
Question
id ____ PRIMARY KEY
Answer
id SERIAL PRIMARY KEYExplanation: SERIAL (PostgreSQL) generates an automatically increasing number for each new row.
3 What is the trait of a surrogate key?
- It has no business meaning Correct answer
- It can be NULL
- It repeats
- It is historical
Explanation: A surrogate key has no business meaning.