Keys in a Database: Primary, Candidate, Alternate and Foreign Key

Hello students, welcome to Kwickprep. Your class has two students named Riya Shah. The fee office writes, Riya Shah paid. Which Riya paid? Today we will learn how keys solve this problem: candidate keys, the primary key, alternate keys and the foreign key that links two tables.

Look at this table carefully. The first row is Riya Shah of nine A, from Surat. The second row is Aman Verma of nine A, from Pune. The third row is another Riya Shah, also in nine A and also from Surat. The first and third rows look the same, so the database cannot tell the two girls apart.

To fix this, every row needs a unique identifier. That means a value that no other row in the table has. A column that uniquely identifies each row is called a key. Its value can never repeat in two rows. A primary key value can also never be empty, and in databases an empty value is called null.

So the school adds more columns to the Student table. Admission number one zero zero one belongs to the first Riya Shah, with her own email. Admission number one zero zero two belongs to Aman Verma. Admission number one zero zero three belongs to the second Riya Shah, with a different email. Now every row can be told apart.

A candidate key is any column that could serve as the unique identifier of a row. A table can have more than one of them. Admission number never repeats, so it is a candidate key. Every student has a different email, so email is also a candidate key. Name repeats, because there are two Riya Shahs, so name is not a candidate key.

From the candidate keys, we choose exactly one to be the main identifier. That chosen key is called the primary key. It must never be empty, and every student gets an admission number on the first day. It should rarely change, but a student can change an email address any time. It should be short and simple. So admission number is the best choice for the primary key.

Pause and predict, then check your answers. Can City be the primary key? No, because many students live in the same city. Can Class be the primary key? No, because a whole class shares nine A. Can a table have two primary keys? No, a table has only one primary key, although that key may be made of two columns together.

What happens to the candidate keys we did not choose? A candidate key that is not chosen as the primary key is called an alternate key. In the Student table, we chose admission number, so email becomes the alternate key. Remember this simple formula: candidate keys are the primary key plus all the alternate keys.

Let us put it all together for the Student table. Admission number is a candidate key, and we chose it as the primary key. Email is a candidate key, but not chosen, so it is the alternate key. Name is not unique, so it is not a key at all.

Now the fee office keeps a second table called Fees. Receipt five zero one says admission number one zero zero one paid twelve thousand rupees. Receipt five zero two says one zero zero three paid twelve thousand. Receipt five zero three says one zero zero one paid three thousand more. The Fees table does not store names, only admission numbers.

The admission number column in Fees is a foreign key. A foreign key is a column in one table that refers to the primary key of another table. Here, admission number in Fees points to admission number in Student. Unlike a primary key, a foreign key can repeat, because one student can pay many times. But every value must already exist in Student, so no fee is recorded for a student who does not exist.

Watch the foreign key link the two tables, step by step. We read receipt five zero two from the Fees table. We take its admission number, one zero zero three. We look for one zero zero three in the Student table, and it is there. So the receipt belongs to the second Riya Shah, and our confusion is solved. If the number were missing, the database would reject the receipt.

Let us revise what we learned today. A key uniquely identifies each row in a table. Candidate keys are all the columns that could do that job. The primary key is the one we choose, and it never repeats and is never empty. Alternate keys are the candidate keys we did not choose. A foreign key refers to the primary key of another table, and it links the two tables together.