Candidate key, super key and composite key

A super key is any set of columns whose values are unique for every row. It may include extra columns that are not needed. In a table of students, {student_id} is a super key, and so is {student_id, name}, even though name adds nothing.

A candidate key is a smallest possible super key: remove any column and it stops being unique. If a student table has both student_id and email, and each is unique on its own, then both are candidate keys.

The primary key is the one candidate key you choose to be the main identifier. The others are called alternate keys and are usually enforced with a UNIQUE constraint.

A composite key is a key made of two or more columns. It is needed when no single column is unique. In an enrolment table, one student takes many courses and one course has many students, but each pair appears once:

CREATE TABLE enrolments (
    student_id INT,
    course_id  INT,
    joined_on  DATE,
    PRIMARY KEY (student_id, course_id)
);

Neither column is unique alone, but together they identify one row.

How to answer in an interview

1. 1Start with the super key, because every other key is a super key with extra conditions.
2. 2Say that a candidate key is a minimal super key.
3. 3Say that the primary key is the candidate key you pick, and that it cannot be NULL.

Also mention the surrogate key: a made-up number such as an auto-increment id that has no business meaning. It is often used as the primary key because real-world values like an email can change.