# Keys in the Relational Model

> Keys in the relational model are columns or sets of columns that uniquely identify rows within a table and connect tables to each other. The main kinds are the super key, candidate key (a minimal super key), primary key (the chosen candidate key), alternate key, foreign key (a reference to another table's primary key) and composite key.

Canonical: https://devsunite.com/resources/core-subjects/dbms/keys-in-relational-model
Last updated: 2026-11-22

Subject: DBMS · Category: RDBMS · 4 min read

Keys are how a relational database uniquely identifies rows within a table and establishes relationships between different tables. Several distinct kinds of keys exist, and mixing up their definitions is one of the most common stumbling points in DBMS interviews.

## The different kinds of keys

**Super key.** Any combination of one or more columns that can uniquely identify a row. A table can have many super keys, including ones with unnecessary extra columns, `(id, name)` is a super key on a table where `id` alone is already unique, even though `name` adds nothing to the uniqueness.

**Candidate key.** A super key with no unnecessary columns, meaning if you removed any single column from it, it would stop being unique. A table can have multiple candidate keys. A `users` table might have both `id` and `email` as separate candidate keys, either one alone is enough to uniquely identify a row.

**Primary key.** One candidate key chosen as *the* official identifier for a table. A table has exactly one primary key. It cannot be null, and its value must be unique across every row.

**Alternate key.** Any candidate key that wasn't chosen as the primary key. If `id` is chosen as the primary key in the `users` example above, `email` becomes an alternate key, still unique, just not the officially designated one.

**Foreign key.** A column (or set of columns) in one table that references the primary key of another table, establishing a relationship between them. A foreign key's values must either match an existing value in the referenced table, or be null (if the relationship is optional).

**Composite key.** A key made up of more than one column together, where no single column alone is unique, but the combination is. In an `order_items` table tracking which products are in which order, `(order_id, product_id)` together might form a composite primary key, since neither column alone uniquely identifies a row, but the pair does.

## A worked example

A `students` table:

| student_id | national_id | email | name |
|------------|--------------|-------|------|
| 1 | A1234 | asha@mail.com | Asha |
| 2 | B5678 | ravi@mail.com | Ravi |

- `{student_id}`, `{national_id}`, and `{email}` are each individually unique, so each is a **candidate key**.
- `{student_id, name}` is a **super key** (it's unique, since `student_id` alone already guarantees that), but not a candidate key, since `name` is unnecessary for uniqueness.
- If the school designates `student_id` as the official identifier, it becomes the **primary key**.
- `national_id` and `email`, still unique but not chosen as primary, become **alternate keys**.

Now add an `enrollments` table:

| student_id | course_id | grade |
|------------|-----------|-------|
| 1 | 101 | A |
| 1 | 102 | B |
| 2 | 101 | A |

Here, `student_id` references `students.student_id`, it's a **foreign key**. And since no single column in `enrollments` is unique on its own (student 1 appears twice), but `(student_id, course_id)` together is unique, that pair forms a **composite primary key** for this table.

## Where this matters in real systems

Choosing the right primary key has real, lasting consequences: using a naturally meaningful but occasionally-changeable value (like an email address) as a primary key can cause real headaches later if that value ever needs to change, since every foreign key referencing it would need to update too. This is exactly why many real schemas use a separate, immutable surrogate key (a simple auto-incrementing `id`) as the primary key, and keep naturally meaningful unique values like email as alternate keys with their own unique constraint instead.

## What interviewers actually ask about keys

- **"What's the difference between a candidate key and a primary key?"** A table can have multiple candidate keys (each independently sufficient to uniquely identify a row); the primary key is the one officially chosen from among them.
- **"What's the difference between a super key and a candidate key?"** A super key can include unnecessary extra columns; a candidate key is minimal, removing any column from it breaks uniqueness.
- **"What is a foreign key, and what can it reference?"** A column referencing the primary key of another table, establishing a relationship; its values must match an existing referenced value or be null.
- **"When would you use a composite key?"** When no single column is unique on its own, but a specific combination of columns is, a common pattern for linking/junction tables like `order_items`.
- **"Why might a surrogate key be preferred over a natural key as a primary key?"** Natural keys (like email) can theoretically change, which is painful to propagate through every foreign key referencing them; a surrogate key (like an auto-incrementing id) is stable and never needs to change.

## Key takeaways

- A super key is any unique-identifying column combination; a candidate key is a minimal one (no removable columns).
- A primary key is the one candidate key officially chosen to identify a table's rows; the rest become alternate keys.
- A foreign key references another table's primary key, establishing a relationship between the two tables.
- A composite key uses multiple columns together to achieve uniqueness that no single column provides alone.
- Surrogate keys (simple auto-incrementing IDs) are often preferred over natural keys as primary keys because they never need to change.

## Related

- [Previous: Introduction to the Relational Model](https://devsunite.com/resources/core-subjects/dbms/introduction-to-relational-model)
- [Next: Features of a Good Relational Database Design](https://devsunite.com/resources/core-subjects/dbms/features-of-good-design)
- [More in RDBMS: What Is an RDBMS?](https://devsunite.com/resources/core-subjects/dbms/what-is-rdbms)
- [More in RDBMS: Introduction to the ER Model](https://devsunite.com/resources/core-subjects/dbms/introduction-to-er-model)
- [More in RDBMS: How to Draw an ER Diagram](https://devsunite.com/resources/core-subjects/dbms/how-to-draw-an-er-diagram)
- [All 174 DBMS interview questions](https://devsunite.com/resources/core-subjects/dbms)
