1. Overview
In this article,we will learn the difference between primary key vs candidate key.
2. Primary key
A primary key is the column or columns that contain values that uniquely identify each row in a table. A database table typically has a primary key to identify a tuple or row.
Primary keys must contain UNIQUE values and cannot contain NULL values.
A table can have only ONE primary key; and in the table, this primary key can comprise single or multiple columns (fields).
3. Candidate key
A candidate key, or simply a key, of a relational database is a minimal superkey. A superkey is a set of attributes that uniquely identify each tuple or row of a relational table. Because superkey values must be unique, tuples (rows) with the same superkey value must also have the same non-key attribute values.
The set of all attributes is always a superkey (the trivial superkey). Tuples in a relation are by definition unique, with duplicates removed after each operation, so the set of all attributes is always unique for every tuple. A candidate key (or minimal superkey) is a superkey that you cannot reduce further to a simpler superkey by removing an attribute. In other words, removing an attribute from the candidate key produce duplicate results and no longer be unique.
For example, in a Student schema with attributes id, name, course, and dept. If id values are unique, then id combined with any or all of the other attributes can uniquely identify tuples in the table. Each of the combinations {id}, {id, name}, {id, name, job}, and {id, name, course, dept} is a superkey. {id} is a candidate key as you cannot reduce it further.
{id, name, job, dept} is the trivial superkey.