Skip to content
Tutorials Center
My learning

Topics

Lesson 68 of 125 · Designing Databases

Primary, Candidate, Composite and Foreign Keys

Learn every kind of SQL key – super, candidate, primary, alternate, composite, surrogate and natural keys – and how to choose the right key for a table.

A key is a column, or a group of columns, whose values let you point at exactly one row. Keys are what make a relational database work: they stop duplicates from sneaking in, they let one table refer to another, and they give the database something fast to look rows up by.

Database people use a whole family of names for keys: super keys, candidate keys, primary keys, alternate keys, composite keys, surrogate keys, natural keys and foreign keys. The names sound academic, but each one answers a practical question you face every time you design a table. In this lesson you'll learn what each term means, how to spot keys in real data, and how to choose a good primary key.

You already saw keys at a glance in Databases, Tables and Keys and wrote your first PRIMARY KEY in CREATE TABLE. Now we go deeper.

What makes something a key?

A key is defined by one rule: no two rows may ever share the same value for it. "Ever" matters. A column that happens to be unique in today's data is not automatically a key. It's a key only if the real world guarantees uniqueness forever, and you tell the database to enforce it.

Take the students table in the school database. Is first_name a key? Look at the data:

SQL Is first_name unique? · database: school

Press Run (or Ctrl + Enter) to see the result here.

Several first names repeat (there are four students called Chen), so first_name can't identify a student. What about first_name plus last_name? There happen to be no duplicates in this small school today, but a new "Nico Kaya" could enroll tomorrow. Names are never keys.

A quick way to test a candidate is to compare the number of rows with the number of distinct values. If they differ, the column is definitely not a key:

SQL Testing columns for uniqueness · database: school

Press Run (or Ctrl + Enter) to see the result here.

All 36 full names happen to be distinct today, while only 20 last names are. The data can disprove a key (36 rows but 20 distinct last names), but it can never prove one. Only knowledge of the domain can: the school assigns every student a unique ID, so id is a key by design.

Super keys and candidate keys

A super key is any set of columns whose combined values are unique. In students, id is a super key. So is (id, last_name), and so is (id, first_name, birth_date), because adding columns to something unique keeps it unique. Most super keys carry passengers: columns that add nothing.

A candidate key is a minimal super key: you can't remove any column without losing uniqueness. id is a candidate key. (id, last_name) is not, because id alone is already unique.

A table can have several candidate keys. In the school's courses table, both id and code (like 'MATH101') identify a course:

SQL
CREATE TABLE courses (
  id INTEGER PRIMARY KEY,         -- candidate key #1 (chosen as primary)
  code TEXT NOT NULL UNIQUE,      -- candidate key #2 (alternate key)
  title TEXT NOT NULL,
  teacher_id INTEGER REFERENCES teachers(id),
  credits INTEGER NOT NULL,
  capacity INTEGER NOT NULL
);

Each candidate key is a real business rule: "no two courses share an ID" and "no two courses share a code". Both deserve enforcement.

Primary keys and alternate keys

From the candidate keys you pick one to be the primary key: the official identifier other tables use to refer to a row. Every table should have exactly one. The others become alternate keys, which you enforce with UNIQUE (and usually NOT NULL).

The primary key comes with promises:

  • Unique: no two rows share it.
  • Not null: every row has one. A row with an unknown identity can't be referenced.
  • Stable: it shouldn't change once assigned, because other tables store copies of it.

Watch both kinds of key reject duplicates. The last statement below fails on purpose:

SQL Primary and alternate keys at work · database: school

Press Run (or Ctrl + Enter) to see the result here.

The run stops with Error: UNIQUE constraint failed: courses.code. Change the code to something new and both rows go in. Try reusing id 11 instead and you'll see the primary key complain about courses.id.

SQLite quirk: primary keys can hold NULL. Because of a bug kept for backward compatibility, SQLite allows NULL in a PRIMARY KEY column unless the column is INTEGER PRIMARY KEY, the table is STRICT or WITHOUT ROWID, or you add NOT NULL yourself. PostgreSQL, MySQL and SQL Server always forbid it. Write NOT NULL on non-integer primary keys to be safe everywhere.

SQL The NULL primary key quirk

Press Run (or Ctrl + Enter) to see the result here.

Two rows with a NULL key, and nothing complained. Change the column to code TEXT NOT NULL PRIMARY KEY and run it again: the first NULL insert now fails with NOT NULL constraint failed: countries.code.

Composite keys

A composite key (or compound key) is a key made of two or more columns. No single column is unique, but the combination is. You declare it with a table-level PRIMARY KEY (...) clause.

Composite keys are natural for tables that connect other tables. In the company database, an employee can work on many projects and a project has many employees. The assignments table records each pairing once:

SQL
CREATE TABLE assignments (
  employee_id INTEGER NOT NULL REFERENCES employees(id),
  project_id INTEGER NOT NULL REFERENCES projects(id),
  role TEXT NOT NULL,
  hours_per_week INTEGER NOT NULL,
  PRIMARY KEY (employee_id, project_id)
);

Employee 6 appears twice, and project 1 appears many times, but the pair (6, 1) appears only once:

SQL A composite primary key · database: company

Press Run (or Ctrl + Enter) to see the result here.

The first query shows the existing rows, then the INSERT fails with UNIQUE constraint failed: assignments.employee_id, assignments.project_id. Notice the error names both columns: the key is the pair.

The school's enrollments table goes one step further with a three-column key, (student_id, course_id, term_id). A student may take the same course again in a later term, but not twice in the same term. Choosing the columns of a composite key is choosing a business rule, so think it through: a key of just (student_id, course_id) would make retaking a course impossible.

Column order inside a composite key matters for performance, because the database builds an index in that order. A key on (employee_id, project_id) makes "all projects of employee 6" fast, but not "all employees on project 1". You'll see why in Indexes.

Natural keys and surrogate keys

Where do key values come from? There are two schools.

A natural key is a value that already exists in the real world and is unique there: an ISO country code ('JP'), a currency code ('EUR'), an airport code ('LIS'), a book's ISBN, a course code. A surrogate key is a value invented by the database purely to identify rows, usually an auto-incrementing integer (1, 2, 3 …) or a UUID. It means nothing outside the database.

Natural key Surrogate key
Example 'EUR', 'MATH101', ISBN 42, a UUID
Meaning Readable, means something to people Meaningless by design
Stability Can change if the real world changes Never needs to change
Size Can be long text or several columns Small integer (fast joins)
Uniqueness Only as good as the outside authority Guaranteed by the database

Natural keys look attractive, but the real world is messier than it seems. Email addresses change. People change names. Product codes get reorganized. Government ID numbers get reissued, and many people don't have one. If a natural key changes, every table that stores a copy of it must change too.

That's why most application tables use a surrogate primary key and keep the natural key as an alternate key with UNIQUE. You get stable, compact references plus protection against duplicates. The shop's customers table does exactly this:

SQL Surrogate key plus a unique natural key · database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first query lists three customers, then the INSERT fails on purpose with UNIQUE constraint failed: customers.email, because Rosa Bauer already has an account. Orders refer to customers by id, so if Rosa changes her email, only one row changes. The UNIQUE constraint on email still makes sure two accounts can't share an address.

Always enforce the natural key too. A surrogate key alone guarantees nothing about real duplicates: without UNIQUE (email) you could insert the same customer a hundred times, each with a fresh id. The surrogate key identifies rows; the natural key protects facts.

Small, stable lookup tables are the classic place where a natural primary key shines. A currencies table keyed by 'EUR', 'USD' and 'JPY' makes child rows readable without a join. You'll learn how the database generates surrogate values in Auto-Increment and Identity Columns.

Foreign keys

A foreign key is a column in one table that holds the primary key (or another candidate key) of a row in another table. books.author_id in the bookstore is a foreign key pointing at authors.id. It's the glue between tables, and it's how joins know what to match.

A foreign key is not unique in its own table: many books can have the same author. Its job is different. It promises that every value refers to a row that really exists.

SQL Following a foreign key · database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Foreign keys have enough rules of their own (what happens when the parent row is deleted, how to switch enforcement on in SQLite) to fill a whole lesson: see Foreign Keys.

Choosing a primary key

When you design a new table, run through these questions:

  1. What is one row? Write it down: "one row = one customer", "one row = one product in one order". The key identifies exactly that.
  2. What are the candidate keys? List every column set that the real world guarantees to be unique. Enforce each one.
  3. Is there a short, stable natural key? For small reference tables (countries, currencies, status codes), use it as the primary key.
  4. Otherwise, add a surrogate key. An integer id is the usual default. Keep the natural keys as UNIQUE alternate keys.
  5. For pure linking tables, a composite key of the two foreign keys is often the best choice, because it enforces "each pair once" for free.

And avoid these common mistakes:

  • Using a value that can be empty or unknown (a phone number, a middle name).
  • Putting meaning into a surrogate key, such as '2025-INV-00042', then having to change it.
  • Using a floating-point column as a key: rounding makes equality unreliable.
  • Forgetting the UNIQUE constraint on the natural key "because we have an id".

Check your understanding

Quick check · 5 questions

of 5 answered You scored / 5 · perfect!

  1. 1. A table has the columns (id, email, name), where id and email are each unique. Which statement is true?

    Each column is unique on its own and can't be made smaller, so each is a minimal super key, which is a candidate key. (id, email) is a super key but not minimal.
  2. 2. In the company database, what does PRIMARY KEY (employee_id, project_id) on assignments allow?

    A composite key is unique as a combination. Each column may repeat on its own; only the pair must be unique.
  3. 3. Why do most application tables use a surrogate key instead of an email address as the primary key?

    A primary key should be stable because other tables store copies of it. Text keys are allowed, but a changing natural key is painful to update everywhere.
  4. 4. You run this in SQLite: CREATE TABLE t (code TEXT PRIMARY KEY); INSERT INTO t VALUES (NULL); INSERT INTO t VALUES (NULL); SELECT COUNT(*) FROM t; What is the result?

    SQLite allows NULL in non-INTEGER primary keys for backward compatibility, and NULLs never count as duplicates. Add NOT NULL to prevent it.
  5. 5. Which of these is the best primary key for a small currencies table?

    ISO currency codes are short, unique and stable, which makes them a good natural key. Names can be translated or changed, and rates change constantly.

Summary

  • A key identifies exactly one row; uniqueness must be guaranteed by the domain, not just true in today's data.
  • A super key is any unique column set; a candidate key is a minimal one.
  • The primary key is the chosen candidate key: unique, not null and stable. The others are alternate keys, enforced with UNIQUE.
  • A composite key combines several columns; it's ideal for linking tables like assignments and enrollments.
  • Natural keys come from the real world; surrogate keys are generated by the database. Use a surrogate primary key plus a UNIQUE natural key for most tables.
  • A foreign key refers to a key in another table and holds tables together.
  • In SQLite, add NOT NULL to non-integer primary keys.

Next: Data Types, where you'll choose the right type for every column.