# Primary, Candidate and Alternate key


Keys in DBMS are quite confusing things. So, let’s break it down into simple.

If you don’t like reading the things out then check this video, I got you covered.

%[https://www.youtube.com/watch?v=V793yFQpTFM]


## So, what is a key ?

A key is nothing but an attribute or set of attributes that are able to uniquely identify each record in table. (*I hope here you know about database and table already.)*
> Let’s understand more with some examples

![Student table](https://cdn.hashnode.com/res/hashnode/image/upload/v1627310186725/ckUxMvz7C.png)*Student table*

Here, we have this electronic table that basically has different **rows** and **columns**. So if we want to get information about any of the **students** then we need an **attribute** or a **property** by the help of which, we’re gonna get the information. We can use here, ***name*** as a property to get that information. If we want to get the details of **Andy** then we write a SQL query like this:
> *SELECT * FROM students WHERE name = ‘Andy’;*

which returns the details of **Andy**. But what if we have to deal with larger records?

![Students in a classroom](https://cdn.hashnode.com/res/hashnode/image/upload/v1627310188735/NMR2JAXbo.png)*Students in a classroom*

Here, we have a table of a **classroom** that has 17 students. If we observe carefully, we can find that there are students with the **same name**. If we try to get information about **Andy**, we’re gonna get information about both students who live in **CA** and **TX**. But we want to get information about **Andy**, who lives in **CA**, not in **TX**. For that a simple solution would be, we write the full name of a student.

![Students in a classroom with full name](https://cdn.hashnode.com/res/hashnode/image/upload/v1627310190976/idFOaFInH.png)*Students in a classroom with full name*
> SELECT * FROM students WHERE First_Name = ‘Andy’ AND Last_Name = ‘Thomas’;

Now, the above query is using both **First_Name** and **Last_Name** (*fullname*) to identify each student in a classroom so it will return only one **Andy** who lives in **CA**.

Ok, we solved the issue with **Andy**. But what if we want the information about **Henry** who lives in **NY**. Again the same problem, now we have to search for the **new property** or so called attribute which is capable of identifying each student **uniquely**.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1627310193340/EK0MoHlV7.png)

If we take **Address** as an unique identifier, is that going to help us? **Obviously not** because many students can live in the **same place**.

Now if we observe here, **Roll no** or **Phone no. **number can uniquely identify each student in a table. So, these attributes are the **unique identifiers**. It is not mandatory that we have to choose a single attribute as an unique identifier, we can choose a **group of attributes** to identify each **record uniquely**. For example, we can group **First_Name** and **Last_Name**, and call them as an unique identifier. *(As we did earlier)*

By the definition of **candidate key**, we have three candidate keys *( Roll no, {First_Name, Last_Name} & Phone no.)* in this table. And we have to choose one candidate key as a **primary key**. But you need to look at a few things before setting any candidate key as a primary key.

## One of them is:

**Primary key cannot have a null value but candidate key can.**

In this example, the **Phone no.** can be null since a student *may* or *may not* have a phone. But neither **Roll no** nor the **name** of the student can be null. So, **Roll no** and fullname ( *{First_Name, Last_Name} *)are the winners here.

Also if the number of student increases, then there is a possibility that some of the students can have the same **First_Name** and **Last_Name**, so the name as a whole can’t even be considered as a candidate key.

So, what we are now left off ? **Clearly**, **Roll no** can be considered as a primary key in the above example.

![Our keys in this example](https://cdn.hashnode.com/res/hashnode/image/upload/v1627310195475/3Fy6oBdaZ.png)*Our keys in this example*

If **candidate keys** are the attributes which uniquely identify each record in a table. Like in this case, we have **Roll no** and **Phone no**.. And a capable candidate key is the **primary key**. Then what the heck are these **alternate keys**? Well it turns out that the candidate keys which weren’t able to become a primary key are considered as alternate keys. In the above example, **Phone no.** is an alternate key.

## **Quick notes:**

## Candidate keys:

The attribute or set of attributes which uniquely identify each record (row) in a table are called candidate keys. In other words, the minimal super key is a candidate key.

## Primary key:

The attribute or set of attributes which uniquely identify a single row in a table is called primary key. A table(relation) can have only one primary key and it cannot be null. Most of the time, update operation isn’t performed in primary key.

## Alternate keys:

Candidate keys that has not been selected to be the primary key are called alternate keys. Alternate keys are also known as **secondary keys**.