Surrogate keys

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.

Exercise Surrogate key

Create the products table with id SERIAL PRIMARY KEY and name TEXT.

Show example solution

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

Test yourself

Surrogate keys: match the terms

surrogate

artificial
SERIAL

auto
business

domain
stable

stable

Auto-incrementing key

id ____ PRIMARY KEY

Show answer

id SERIAL PRIMARY KEY

What is the trait of a surrogate key?
- It has no business meaning
- It can be NULL
- It repeats
- It is historical
Show answer

It has no business meaning — A surrogate key has no business meaning.

Next lesson: Natural keys

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies