Keys: what makes a row that row

A key answers one question: given these columns, is there exactly one row? Every idea after this one is built on it, so it is worth being precise.

Super key. Any set of columns that identifies a row uniquely. {roll_no} is one. So is {roll_no, name}, which is a super key with a passenger.

Candidate key. A super key with no passengers. Remove any column and it stops being unique. A table can have several: roll_no and email are both candidate keys for students.

Primary key. The candidate key you chose. One per table, never null.

Alternate key. The candidate keys you did not choose.

Foreign key. A column pointing at another table's primary key. This is what makes referential integrity possible: the database refuses a student whose department does not exist, and refuses to delete a department that still has students.

CREATE TABLE students (
    roll_no  INT PRIMARY KEY,
    email    VARCHAR(120) UNIQUE NOT NULL,   -- alternate key
    dept_id  INT NOT NULL REFERENCES departments(id)
);

Choosing a primary key: prefer something small, never-changing and never null. Email is unique but people change it, and every foreign key pointing at it would have to change too. That is why roll_no wins.

Takeaway: a candidate key is minimal and unique. Everything about normal forms is a statement about which columns depend on which keys.