What is the difference between a primary key and a candidate key?
Interview preparation resource from Gate Smashers.
A candidate key is any minimal set of attributes that can uniquely identify a row. The primary key is the one candidate key selected as the main identifier for that table. A table can have multiple candidate keys; the chosen one is the primary key and the others are alternate keys. Every primary key is a candidate key, but not every candidate key is chosen as the primary key. Primary key cannot contain NULL.
Definitions
Candidate key: Any minimal set of attributes that can uniquely identify a row in the table. 'Minimal' means no attribute can be removed without losing uniqueness.
Primary key: The single candidate key chosen by the database designer to serve as the table's main identifier.
Minimality and uniqueness
Each candidate key must uniquely identify rows and be minimal. There can be multiple candidate keys for a table when different attribute sets each uniquely identify rows.
The primary key is one of those candidate keys and therefore also uniquely identifies rows.
Example
Suppose a Student table has roll_no, email, and phone, and all three are individually unique. Then each of {roll_no}, {email}, and {phone} is a candidate key. The designer might choose roll_no as the primary key; the remaining candidate keys become alternate keys.
- Candidate keys: {roll_no}, {email}, {phone}
- Primary key: roll_no (chosen example)
Choosing a primary key
A good primary key is usually stable, unique, non-null, and preferably small. The primary key imposes a single primary key constraint on the table.
Remember: every primary key is a candidate key, but not every candidate key is selected as primary; the unselected candidate keys are often called alternate keys.
- Good primary key traits: stable, unique, non-null, preferably small