Keys

Two students walk into the office. Both are called Aisha. Both are in second year. Both study DBMS 101.

You need to give one of them a grade. Which one?

Why & what

You can't tell them apart, so you can't do anything safely. Not update, not delete, not grade.

Every row in a table needs some way to be picked out with zero doubt.

Names don't do it. Nor does a combination of ordinary facts, since two people can share all of them. You need something guaranteed unique.

A key is a column, or a set of columns, whose values are always unique — so any one row can be picked out exactly.

There are a few kinds. They sound like five separate ideas but they're really one idea with filters applied.

How it works

You already know what a table and a tuple are. Now let's find the keys in Student(roll_no, name, email).

1. Start wide — super key. Any set of columns that happens to be unique is a super key. roll_no is one. So is (roll_no, name). So is (roll_no, name, email). All unique — but the last two are carrying dead weight, because roll_no alone was already enough.
2. Trim the dead weight — candidate key. — A candidate key is — a super — key — with — nothing spare in it. Remove any column and it stops being unique. Here we have two: roll_no, and email. Both identify a student on their own.
3. Pick one — primary key. You choose one candidate to be the official identifier. That's the primary key. We pick roll_no. It can never be empty and never repeat.
4. The leftovers — alternate key. email was a perfectly good candidate but didn't get the job. That makes it an alternate key. It's still unique — just not the official one.
5. Point at another table — foreign key. In Enrollment, the column roll_no isn't identifying the enrollment. It's pointing at a row in Student. That's a foreign key. You met this in 1.2: it's what makes the database reject roll 999.

One line to hold all five: super key = unique. Candidate key = unique and minimal. Primary key = the candidate you chose. Alternate key = the ones you didn't. Foreign key = a pointer to someone else's primary key.

Notice: the boxes nest — every primary key is also a candidate key.

Common confusion

The big one: super key vs candidate key.

Both are unique. The difference is whether anything in there is useless.

• (roll_no, name) — unique, so it's a super key. But name is doing no work. Not a candidate key.
• roll_no — unique, and you can't remove anything. Candidate key.

So every candidate key is a super key, but not the other way around. That's exactly why the diagram nests the boxes.

Second confusion: people think a composite key is its own separate category. It isn't.

"Composite" just means the key is made of more than one column — like Enrollment(roll_no, course_code), where you need both together to identify one enrollment. A composite key can be a candidate key or a primary key. It's a description of shape, not a new type.

Third: can a foreign key be NULL? Yes, usually — a NULL foreign key just means "this row doesn't point at anything yet." A student with no locker assigned. A primary key can never be NULL. People mix these up constantly, so the contrast is worth memorising: primary key never empty, foreign key may be empty.

Interview angle

1. "Difference between a super key and a candidate key?" — Both unique; a candidate key has nothing extra in it. Give the (roll_no, name) vs roll_no example — the example is what proves you get it.
2. "Can a table have more than one candidate key? More than one primary key?" — Many candidate keys, yes (roll_no and email). Only ever one primary key, though it may be built from several columns.
3. "Can a foreign key be NULL? Can a primary key?" — Foreign key yes (nothing linked yet). Primary key never — a row with no identity can't be found or updated.
4. "How would you pick between two candidate keys?" — Prefer the one that's short, never changes, and is never unknown at insert time. Roll number beats email, because people change their email.

Recap

A key identifies rows uniquely — super key (unique), candidate key (unique and minimal), primary key (the chosen candidate, never NULL), alternate key (the unchosen ones), foreign key (a pointer to another table's primary key).