MySQL + SQL · Lesson 18
Candidate Key, Super Key and Alternate Key
Types of Keys
Beyond the primary key, DBMS defines super key, candidate key, alternate key and composite key. They are a common exam topic.
Definitions
| Key | Meaning |
|---|---|
| Super key | Any set of columns that uniquely identifies a row (may have extra columns) |
| Candidate key | Minimal super key — no extra column; can become primary key |
| Primary key | The candidate key chosen as main identifier |
| Alternate key | Candidate keys not chosen as primary |
| Composite key | Key made of two or more columns |
Example
In students(roll_no, admission_no, email, name): roll_no, admission_no and email are each candidate keys. If roll_no is chosen primary, then admission_no and email become alternate keys. (roll_no, name) would be a super key.
Summary
- Super key ⊇ candidate key ⊇ primary key.
- Unused candidate keys = alternate keys; multi-column key = composite key.