Keys

Super, candidate, primary, alternate, and foreign keys — how a database tells rows apart and links tables together.

Keys are how a database tells rows apart and links tables together. Any set of columns whose value is different for every row — so it can never confuse two rows — is a super key. Trim a super key until nothing is wasted and you have a candidate key. Pick one candidate to be the official identifier and it becomes the primary key; the leftovers are alternate keys.

The nesting — from loosest to strictest

1Super key — *any* set of columns that identifies rows uniquely, even if it drags along extra, unnecessary columns.
2Candidate key — a super key with nothing to spare: remove *any* column and it stops being unique. A table can have several.
3Primary key — the one candidate key you choose as the official row identifier. It must be unique and never NULL.
4Alternate key — a candidate key that wasn't chosen; still unique, usually enforced with a UNIQUE rule.

A worked example

Table Student(roll_no, email, name, dept_id). Both roll_no and email are unique, so each is a candidate key. {roll_no, name} is a super key (unique, but name is dead weight). Choose roll_no as the primary key → email becomes the alternate key.

CREATE TABLE Student (
  roll_no INT PRIMARY KEY,          -- chosen candidate = primary key
  email   VARCHAR(80) UNIQUE,       -- other candidate = alternate key
  name    VARCHAR(60),
  dept_id INT REFERENCES Department(dept_id)  -- foreign key
);

Foreign keys are a different animal

The keys above all *identify* their own rows. A foreign key does the opposite job: it doesn't identify anything, it points at another table's primary key to link the two tables and keep them consistent. Above, dept_id in Student points at Department(dept_id) — every student's department must be a real one.

A primary key can never be NULL

NULL means 'no value'. A primary key must be unique *and* not NULL, because a row with no identifier could never be found or referenced. A foreign key, however, may sometimes be NULL — meaning 'not linked yet'.

OperationTimeSpace

Super key · widest setuniquemay have extras

Candidate key · several allowedunique + minimalno removable column

Primary key · the chosen candidateunique + NOT NULLexactly one

Check yourself

In Student(roll_no, email, name, dept_id) with roll_no as primary key and email also unique, what is {roll_no, name}?