Almost every schema design conversation eventually uses the word "key" — primary key, foreign key, candidate key, composite key, super key. These terms describe how rows are uniquely identified and how tables reference one another, and they are the vocabulary every other database design topic (relationships, normalization, indexing) builds on. Getting comfortable with the differences between them now makes everything that follows much easier to reason about.

At its core, a key is simply one or more columns whose values can be used to identify a row, or to link a row in one table to a row in another. The different key terms just describe different roles a set of columns can play.

A super key is any combination of columns that guarantees uniqueness — even if it includes columns that add nothing to that guarantee. For example, in an employees table, the pair (employee_id, email) is a super key, because employee_id alone is already enough to uniquely identify a row; email is just along for the ride.

A candidate key is a super key trimmed down to the minimum — no column can be removed without losing uniqueness. A table often has more than one candidate key. Consider this example:

Here, employee_id is unique, email is unique, and national_id is unique — each one, by itself, could identify a row. Each of these three columns is a candidate key. full_name is not, because two different employees could share the same name; department is definitely not, since many employees share a department.

Out of the pool of candidate keys, the designer picks exactly one to be the primary key — the column the rest of the schema will use to reference this table. In the employees example, employee_id is the natural choice: it is short, stable, never changes for a given employee, and does not carry personal information the way email or national_id do. A good primary key is unique, never null, and ideally never changes over the row's lifetime.

The candidate keys that are not chosen do not disappear — they are still useful. It is common practice to enforce them with a UNIQUE constraint even though they are not the primary key, so the database still rejects duplicate emails or national IDs.

Sometimes no single column is unique on its own, but a combination of columns is. That combination is a composite key. A classic example is a table that records enrollments: a given student can enroll in many courses, and a given course has many students, but the pair (student_id, course_id) together is unique — a student cannot enroll in the exact same course twice.

A foreign key is a column, or set of columns, in one table that points to the primary key of another table. It is the mechanism that lets a relational database represent relationships between tables (covered in depth on the next page) instead of duplicating data everywhere.

With this constraint in place, the database itself guarantees that every department_id stored in employees actually exists in departments — it is impossible to assign an employee to a department that does not exist, and (depending on the configured behavior) deleting a department that still has employees will either be blocked or cascade, rather than silently leaving orphaned data.