LambdaLabTM
Databases & SQL · Class 12 · Database Concepts
databaseskeys⏱️ 10 min read

Primary, Candidate & Alternate Keys

A table full of students is not much use if you cannot point at one of them and be sure which one you mean. Two students can share a name, a class, a city and even a birthday. Something has to tell them apart, and that something is a key.

1Why a key is needed

Suppose the office keeps only these three columns, and two students happen to be called Ravi:

a table with no way to tell two rows apart
namegradecity
Ravi12Gangtok
Ravi12Gangtok
Diana11Kolkata

Now “update Ravi's marks” is an impossible instruction. There is no sentence you can write that means the first Ravi and not the second. The rows are indistinguishable, and the database has no way to help you.

The fix is to add a column that is guaranteed never to repeat — an admission number:

the same table with an id column
idnamegradecity
2035Ravi12Gangtok
2601Ravi12Gangtok
2199Diana11Kolkata

2Primary key

A primary key is a column that uniquely identifies each record in a table.

Three rules follow from the word “uniquely”, and all three are examinable:

1
No duplicates

The same value cannot appear twice. If 2035 is Ravi's, it can never be anyone else's.

2
Only one per table

A table has exactly one primary key — though that key may be built from more than one column.

3
No NULLs

Every row must have a value. A row with no id could not be identified, which defeats the point.

What makes a good primary key
Something that is unique by its nature and never changes. An admission number is ideal. A phone number is a poor choice — people change them, and two siblings may share one. A name is the worst choice of all.

3Candidate keys

Often more than one column could do the job. Take a table of the school's employees:

employee — three columns could each identify a row
idnamedesignationPANcitysalaryAadhaar
1Sunita RaiPGTABCPR1234KSingtam620004111…3333
2Mohan DasPGTBXYPD5678LGangtok580004222…4444
3Tashi BhutiaTGTCDEPB9012MGangtok470004333…5555

id, PAN and Aadhaar are each unique to one person. Any one of them could have been made the primary key. Together they are the candidate keys.

The candidate keys are the set of all columns that can become the primary key.

{ id, PAN, Aadhaar }

Note that name, city and salary are not candidates: two employees can easily share any of them.

4Alternate keys

You pick one candidate to be the primary key. The rest do not stop being unique — they just did not get the job.

The candidate keys that were not chosen as the primary key are the alternate keys.

candidate keys
id · PAN · Aadhaar
chosen → primary key
id
the rest → alternate keys
PAN · Aadhaar
The relationship, in one line
Every primary key and every alternate key is a candidate key. The primary key is the one that was chosen; the alternate keys are all the others. So candidate = primary + alternate.

5Composite key

Sometimes no single column is unique. Here nobody has an admission number, and names repeat:

students — name alone is not enough, so three columns act together
namefathermotherdobgrade
RaviSureshKamla2008-08-1312
RaviMaheshSita2008-11-0212
DianaPeterMary2009-05-1611

name repeats. But name together with father and mother does not. A group of columns acting jointly as the primary key is a composite key (or composite primary key).

It is still one primary key
A composite key does not break the “only one primary key per table” rule. The table has a single primary key; it just happens to be made of three columns instead of one.

6All four keys together

The keys in this chapter
KeyWhat it isIn the employee table
Candidate keyEvery column that could uniquely identify a row{ id, PAN, Aadhaar }
Primary keyThe candidate actually chosen to identify rowsid
Alternate keyThe candidates that were not chosenPAN, Aadhaar
Composite keySeveral columns acting together as the primary key, used when no single column is uniquenot needed here — id is enough on its own
Quick Check

How many primary keys can a table have?

Quick Check

A table has candidate keys { rollno, aadhaar, email } and rollno is made the primary key. What are aadhaar and email?

Quick Check

Which of these can a primary key column NOT contain?

Quick Check

In a table of match results, neither team_name nor match_date is unique on its own, but together they are. Together they form…

← Previous
🧱 Table, Row & Column
Next →
Foreign Key 🔗