Candidate, Super and Alternate Key in DBMS
In this lesson: Difference between super key, candidate key and alternate key in DBMS with one table example and comparison chart.
How these keys relate
Super, candidate, primary and alternate keys sound similar but form a clear hierarchy. Super keys are the broadest, candidate keys narrow them down, one candidate becomes the primary key, and the rest become alternate keys. Understanding the order makes them easy.
Super key
A super key is any set of columns that can uniquely identify a row — even if it has extra columns. For a students table where roll is unique, all of these are super keys:
{roll}
{roll, name}
{roll, name, marks}
Each uniquely identifies a row, but the last two carry unneeded columns.
Candidate key
A candidate key is a minimal super key — no extra columns. From the super keys above, only {roll} is a candidate key, because removing any column would break uniqueness. A table can have several candidate keys.
Primary key
The primary key is the one candidate key you choose as the official identifier. It cannot be null and there is only one per table.
CREATE TABLE students (
roll INT PRIMARY KEY,
email VARCHAR(100) UNIQUE
);
Here roll is chosen as the primary key.
Alternate key
An alternate key is a candidate key that was not chosen as the primary key. In the table above, if both roll and email can identify a student, and roll is primary, then email is the alternate key. It still uniquely identifies rows.
A worked example
A students table with roll, email, name, marks, where both roll and email are unique:
- Super keys: {roll}, {email}, {roll, name}, {roll, email}, and many more.
- Candidate keys: {roll} and {email} (both minimal).
- Primary key: {roll} (chosen).
- Alternate key: {email} (the other candidate).
Common mistakes
- Calling every super key a candidate key. Only minimal super keys qualify.
- Thinking there is one candidate key. A table can have several.
- Forgetting alternate keys. The unchosen candidate keys are alternate keys.
- Confusing primary and candidate. Primary is the chosen candidate key.
Summary
- A super key identifies a row, possibly with extra columns.
- A candidate key is a minimal super key (no extras).
- The primary key is the chosen candidate key, never null.
- Alternate keys are the candidate keys not chosen as primary.
- The order: super → candidate → primary + alternate.
Next, see all key types in keys in DBMS explained, or the two most used in primary key vs foreign key.
