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.
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
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
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
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.
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
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.
