Chapter 10: Database Key Concepts — Primary Keys, Foreign Keys, Composite Keys, Surrogate Keys

What you'll learn in this chapter

Explain, in your own words, the differences between primary keys, foreign keys, candidate keys, and composite keys
Understand the trade-offs between surrogate keys and natural keys, and choose between them based on the situation
Distinguish the behaviors of a foreign-key constraint's ON DELETE / ON UPDATE (CASCADE / RESTRICT / SET NULL)

Prerequisites

You should understand Chapter 7: Basic Terminology and Naming Conventions and Chapter 9: The Flow of Design.

The Big Picture of Keys

A relational database has several different "keys," each serving a different purpose.

Term	Role
Candidate key	An attribute (or combination) that could uniquely identify a record
Primary key	The "official identifier" chosen from among the candidate keys
Alternate key	Other candidate keys not chosen as the primary key (protected with a UNIQUE constraint)
Foreign key	An attribute that references another table's primary key
Composite key	A key made up of multiple columns together
Surrogate key	A meaningless identifier the system generates automatically (e.g., a sequential ID)
Natural key	A business-meaningful attribute itself used as the key (e.g., an ISBN or tax ID)

Candidate Key — An Attribute With Uniqueness

A candidate key is an attribute (or combination of attributes) that can uniquely identify a record. There are two conditions:

Uniqueness: the value doesn't repeat within the same table
Minimality: it's made up of the minimum necessary attributes (no extra attributes included)

Example: Finding Candidate Keys in an Employee Table

Consider an employee table like this.

Employee ID	Email	SSN	Name	Phone
1001	yamada@example.com	123-45-6789	Taro Yamada	090-...
1002	suzuki@example.com	234-56-7890	Hanako Suzuki	080-...

Let's consider which columns could be candidate keys.

Candidate	Uniqueness	Is it a candidate key?
Employee ID	Assigned uniquely by the company	✓ Candidate key
Email	Different per person	✓ Candidate key
SSN	Assigned uniquely by the government	✓ Candidate key
Name	People can share the same name	✕ Not a candidate key
Phone	Can change / can be shared	✕ Not a candidate key

As you can see, a single table can have multiple candidate keys. You choose the "primary key" from among them.

Primary Key — The Table's "Official Identifier"

The primary key is the identifier chosen from among the candidate keys, decided as "this is what makes a row unique in this table."

Good and Bad Examples of a Primary Key

How to choose a primary key
-- Good example: an auto-numbered surrogate key as the primary key, a business code protected with UNIQUE
CREATE TABLE products (
    id            SERIAL PRIMARY KEY,                       -- surrogate key
    code          VARCHAR(20) UNIQUE NOT NULL,              -- business key (alternate key)
    name          VARCHAR(200) NOT NULL,
    price         NUMERIC(10, 2) NOT NULL
);

-- Bad example: an email address as the primary key
CREATE TABLE customers_bad (
    email         VARCHAR(255) PRIMARY KEY,                 -- email addresses change
    name          VARCHAR(100),
    phone         VARCHAR(20)
);

If you make the email address the primary key, you'll need to rewrite customers.email whenever a user changes their email. Worse, every row in every table that references this email as a foreign key (like orders.customer_email) would also need to be rewritten, making referential integrity a real maintenance burden.

The principle is "choose something for the primary key that doesn't change."

Foreign Key — A Reference to Another Table

A foreign key is a value that points to another table's primary key, expressing a relationship between tables.

Defining a foreign key (PostgreSQL)
CREATE TABLE departments (
    id        SERIAL PRIMARY KEY,
    name      VARCHAR(50) NOT NULL,
    location  VARCHAR(100)
);

CREATE TABLE employees (
    id            SERIAL PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    department_id INTEGER REFERENCES departments(id)
                  ON DELETE RESTRICT
                  ON UPDATE CASCADE,
    hire_date     DATE NOT NULL
);

The Effect of a Foreign Key

INSERT INTO departments (id, name, location) VALUES (10, 'Sales', 'Tokyo');
INSERT INTO departments (id, name, location) VALUES (20, 'Engineering', 'Osaka');

-- OK: references an existing department_id
INSERT INTO employees (name, department_id, hire_date)
VALUES ('Taro Yamada', 10, '2026-01-01');

-- NG: references a department_id that doesn't exist (99)
INSERT INTO employees (name, department_id, hire_date)
VALUES ('Hanako Suzuki', 99, '2026-01-01');  -- ERROR: foreign key constraint violation

This prevents contradictory data — like "an employee with a department ID that doesn't exist" — from getting in (referential integrity).

The Behavior of ON DELETE / ON UPDATE

You can specify how the referencing side (employees) behaves when a row on the referenced side (departments) is deleted or updated.

Action	Behavior
`NO ACTION` (default)	Deleting/updating a referenced row is an error. This is what you get if you specify nothing (PostgreSQL: CREATE TABLE)
`RESTRICT`	Nearly the same as `NO ACTION` (the difference is the timing of the check: `NO ACTION` can be deferred to the end of the transaction, while `RESTRICT` checks immediately)
`CASCADE`	Automatically propagates the change on the referenced side to the referencing side too (on delete, `employees` rows are also deleted; on update, `employees.department_id` is also updated)
`SET NULL`	Sets the referencing column to NULL (the foreign-key column must allow NULL)

When to use ON DELETE CASCADE

CASCADE is convenient, but if the chain of "when the parent goes, the child goes too" spreads too widely, you risk accidentally wiping out a large amount of data. It's safer to limit CASCADE to relationships where the child is clearly dependent on the parent (like an order and its line items). For a case like employees and departments — where "if a department disappears, its employees move to a different department" — RESTRICT is the safer choice, handling the transfer manually before deleting.

Composite Key — A Combination of Multiple Columns

A composite key is a key used when a single column isn't unique on its own, but a combination of multiple columns is unique. It's commonly used on junction tables that represent many-to-many relationships.

An enrollments table (composite primary key)
CREATE TABLE enrollments (
    student_id        INTEGER NOT NULL REFERENCES students(id),
    course_id         INTEGER NOT NULL REFERENCES courses(id),
    enrollment_date   DATE NOT NULL,
    grade             CHAR(2),
    PRIMARY KEY (student_id, course_id)
);

INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
    (1, 101, '2026-04-01'),  -- student 1 enrolled in course 101
    (1, 102, '2026-04-01'),  -- student 1 enrolled in course 102
    (2, 101, '2026-04-01');  -- student 2 enrolled in course 101

-- NG: the same (student_id, course_id) combination can't be duplicated
INSERT INTO enrollments (student_id, course_id, enrollment_date)
VALUES (1, 101, '2026-09-01');  -- ERROR: primary key constraint violation

How to represent many-to-many relationships and design junction tables is covered in detail in Chapter 14: Many-to-Many.

Surrogate Key vs. Natural Key — Modern Best Practice

Surrogate keys and natural keys are two schools of thought on "how to choose" a primary key.

Surrogate Key — An Identifier the System Generates Automatically

An example using a surrogate key as the primary key
CREATE TABLE customers_surrogate (
    id            SERIAL PRIMARY KEY,              -- surrogate key (sequential)
    company_code  VARCHAR(20) UNIQUE NOT NULL,     -- business code
    name          VARCHAR(200) NOT NULL,
    tax_id        VARCHAR(20) UNIQUE NOT NULL      -- tax ID
);

A surrogate key is a value with no business meaning, like SERIAL, BIGSERIAL, or UUID.

Natural Key — A Key With Business Meaning

An example using a natural key as the primary key
CREATE TABLE customers_natural (
    tax_id        VARCHAR(20) PRIMARY KEY,         -- the tax ID as the primary key
    name          VARCHAR(200) NOT NULL,
    phone         VARCHAR(20)
);

Trade-off Table

Aspect	Surrogate key	Natural key
Examples	`SERIAL` / `UUID` / Snowflake ID	ISBN / tax ID / ISO country code / SKU
Immutability	✓ Managed by the system, never changes	△ Can change for business reasons (e.g., an email change)
Length	✓ Short (4-8 bytes)	△ Can be long (ISBN is 13 characters / UUID is 36 characters)
Meaning	✕ You can't tell what row it is just by looking at the value	✓ The value itself carries business meaning
JOIN performance	✓ Fast, since it's an integer comparison	△ Can be slower with string comparison
Risk of exposure in a URL	△ Sequential values risk being guessed (a UUID avoids this)	△ Risk of exposing business information

Modern Best Practice: Hybrid

The standard approach in modern web application development is a hybrid (see Baeldung: Natural vs. Surrogate Keys / MSSQLTips: Surrogate vs. Natural Key).

The primary key is a surrogate key (SERIAL / UUID) — you gain immutability and performance
The business key is protected with a UNIQUE constraint — this also prevents business-level duplicates

A hybrid design (recommended)
CREATE TABLE customers (
    id            SERIAL PRIMARY KEY,                  -- surrogate key (primary key)
    customer_code VARCHAR(20) UNIQUE NOT NULL,         -- business key (alternate key)
    name          VARCHAR(200) NOT NULL,
    email         VARCHAR(255) UNIQUE NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

This lets you reference this table from other tables using customer_id (the surrogate key), while ensuring business-level uniqueness via customer_code or email's UNIQUE constraint.

SERIAL vs. IDENTITY, and INTEGER vs. BIGINT

This guide's code examples prioritize simplicity and use SERIAL (a sequential INTEGER), but in practice you should also consider these two points:

How to write auto-numbering: PostgreSQL 10 and later support the SQL-standard IDENTITY column (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY). The official documentation describes SERIAL as "not a true type, but a notational convenience," so an IDENTITY column is the standards-compliant choice for new designs.
The size of the type: BIGINT (BIGSERIAL) is the safe default for a primary key. INTEGER runs out at about 2.1 billion (see Chapter 3 and the Design Template Collection).
If you make a UUID the primary key: a random UUIDv4 causes index fragmentation. For tables with heavy write volume, consider UUIDv7 instead (see Chapter 19).

Exception: When a Natural Key Is a Good Fit

Hybrid is the general principle, but there are domains where a natural key is stable.

Domain	Example	Reason
ISO country code	`JP`, `US`, `FR`	Fixed by international standard
Currency code	`JPY`, `USD`, `EUR`	Fixed by ISO 4217
ISBN	`978-4-12345-678-9`	Doesn't change once issued (the international standard for identifying books)
A short, immutable lookup table	`status_codes` (around 10 rows)	The values themselves are stable / few enough rows that performance impact is minimal

For a "short string that will absolutely never change" like these, it's fine to use the natural key directly as the primary key.

Default strategy for junior engineers

If you're unsure, go with the hybrid approach: a surrogate key as the primary key, plus a UNIQUE constraint on the business key. Only allow a natural key for things you know are "guaranteed to never change," like an ISO code — that's a safe rule of thumb.

A Key-Design Checklist

A Practical Example: Key Design for an E-Commerce Site

Main tables of an e-commerce site (PostgreSQL)
-- Categories (with self-reference)
CREATE TABLE categories (
    id                  SERIAL PRIMARY KEY,
    name                VARCHAR(100) NOT NULL,
    parent_category_id  INTEGER REFERENCES categories(id),
    display_order       INTEGER NOT NULL DEFAULT 0
);

-- Products (a foreign key to categories; the business code SKU is UNIQUE)
CREATE TABLE products (
    id            BIGSERIAL PRIMARY KEY,
    sku           VARCHAR(50) UNIQUE NOT NULL,
    name          VARCHAR(200) NOT NULL,
    category_id   INTEGER NOT NULL REFERENCES categories(id),
    price         NUMERIC(10, 2) NOT NULL CHECK (price > 0)
);

-- Customers (email is protected with UNIQUE, but isn't the PK)
CREATE TABLE customers (
    id              BIGSERIAL PRIMARY KEY,
    email           VARCHAR(255) UNIQUE NOT NULL,
    password_hash   VARCHAR(255) NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Orders (a foreign key to customers; the order number is UNIQUE for display)
CREATE TABLE orders (
    id              BIGSERIAL PRIMARY KEY,
    order_number    VARCHAR(20) UNIQUE NOT NULL,
    customer_id     BIGINT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    order_date      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    total_amount    NUMERIC(12, 2) NOT NULL CHECK (total_amount >= 0)
);

-- Order line items (composite primary key = order_id + product_id)
CREATE TABLE order_items (
    order_id      BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id    BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity      INTEGER NOT NULL CHECK (quantity > 0),
    unit_price    NUMERIC(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Key points:

Every table's primary key is a surrogate key (SERIAL / BIGSERIAL)
Business keys (sku / email / order_number) get a UNIQUE constraint
orders.customer_id uses ON DELETE RESTRICT (handle customer deletion explicitly)
order_items.order_id uses ON DELETE CASCADE (if the whole order disappears, its line items should too)

Exercises

Exercise 1: Choosing candidate and primary keys

Suppose a books table has the following columns:

id (sequential)
isbn (13 characters, internationally unique)
title (the book's title; duplicates are possible)
publisher_id (the publisher's ID)
published_at (the publication date)

List the columns that could be candidate keys, and say which one would be appropriate to choose as the primary key, along with your reasoning.

Sample answer

Candidate keys:

id (unique, since it's sequential)
isbn (unique, by international standard)
(In some cases, the composite (title, publisher_id, published_at) — different publishers might release a book with the same title, but it's rare for the same publisher to release the same title on the same day)

If choosing a primary key:

A hybrid of id (a surrogate key) as the primary key, plus isbn as a UNIQUE constraint is recommended. Reasons:

id is short and fast as a SERIAL, and efficient for foreign-key references too, since it's an integer comparison
isbn is an international standard, so it might seem like "a natural key is good enough" at first glance, but you might also need to handle books without an ISBN (self-published works, internal documents, used books, some ebooks), making it hard to apply a NOT NULL constraint (a primary key requires NOT NULL)
Choosing isbn as the primary key results in a design that can't handle "books without an ISBN"

CREATE TABLE books (
    id              SERIAL PRIMARY KEY,
    isbn            VARCHAR(13) UNIQUE,                   -- NULL is fine
    title           VARCHAR(500) NOT NULL,
    publisher_id    INTEGER NOT NULL REFERENCES publishers(id),
    published_at    DATE
);

A common mistake: choosing the ISBN as the primary key. It seems reasonable at first — "ISBN is an international standard and never changes" — but if you overlook the possibility that some books you handle won't have an ISBN, you'll run into trouble later.

Exercise 2: Choosing ON DELETE

For the following relationships, decide how to set ON DELETE (CASCADE / RESTRICT / SET NULL), along with your reasoning.

A comments table references the posts table (comments.post_id)
An employees table references the departments table (employees.department_id)
A posts table references the users table (posts.user_id), and you also want to use logical deletion

Sample answer

Case	Setting	Reason
1. comments → posts	`ON DELETE CASCADE`	It's natural for comments to disappear along with a deleted post
2. employees → departments	`ON DELETE RESTRICT`	It would be strange for employees to disappear just because a department is gone. Transfer them explicitly before deleting the department
3. posts → users	`ON DELETE RESTRICT` or `SET NULL`	Depends on the business decision of whether to delete posts along with the user, or keep them attributed to a "deleted user"

A note on case 3: if the UX is "keep posts even after account deletion" for an SNS, one design is to allow user_id to be NULL and set ON DELETE SET NULL. Conversely, if "deleting the account also deletes all their posts," use CASCADE. The right choice depends on business requirements, so this needs to be confirmed with stakeholders.

A common mistake: adding CASCADE everywhere just because "it works for now." This is a breeding ground for accidental, unintended data loss. When in doubt, explicitly set RESTRICT (the safe side), and only allow CASCADE where it's actually needed.

Summary

Here's a recap of what this chapter covered.

A candidate key is "an attribute with uniqueness"; the one chosen as the official identifier is the primary key
Choose a primary key that's "immutable, short, and has no business meaning"
A foreign key references another table's primary key, preserving referential integrity
ON DELETE defaults to NO ACTION if unspecified. When unsure, explicitly set RESTRICT, and limit CASCADE to relationships where the child is "clearly dependent on the parent"
Composite keys are used on junction tables for many-to-many relationships
The modern best practice is a hybrid (surrogate key as PK + natural key as a UNIQUE constraint)

The next chapter covers a design technique for eliminating data duplication and contradictions: normalization.

Next chapter

Chapter 11: Database Normalization

What you'll learn in this chapter​

The Big Picture of Keys​

Candidate Key — An Attribute With Uniqueness​

Example: Finding Candidate Keys in an Employee Table​

Primary Key — The Table's "Official Identifier"​

Good and Bad Examples of a Primary Key​

Foreign Key — A Reference to Another Table​

The Effect of a Foreign Key​

The Behavior of ON DELETE / ON UPDATE​

Composite Key — A Combination of Multiple Columns​

Surrogate Key vs. Natural Key — Modern Best Practice​

Surrogate Key — An Identifier the System Generates Automatically​

Natural Key — A Key With Business Meaning​

Trade-off Table​

Modern Best Practice: Hybrid​

Exception: When a Natural Key Is a Good Fit​

A Key-Design Checklist​

A Practical Example: Key Design for an E-Commerce Site​

Exercises​

Summary​