Alternate Key in DBMS
An alternate key is the secondary candidate key that contains all the property of a candidate key but is an alternate option.
Here, in this section, we will learn about the alternate key, its use and will also look at an example to understand the working of it.
What is an Alternate Key
An alternate is a secondary candidate key that is capable of identifying a row uniquely. However, such a key is not used as a primary key because, as we have discussed in our previous section that out of all the generated candidate keys, only one key is selected as the primary key. Thus, the other remaining keys are known as Alternate Keys or Secondary Keys.
Use of Alternate Key
An alternate key is none other than a candidate key, so the use/role of an alternate key is the same. It means an alternate key is also used to identify those columns in a table that can uniquely identify all the records of the table.
Example of Alternate Key
Let’s discuss some examples to understand the working of an alternate key.
Example 1:
Consider an EMPLOYEE_DETAIL table where we have the following attributes:
Emp_SSN: The SSN number of employees is stored in this field.
Emp_Id: An attribute that stores the value of the employee identification number.
Emp_name: An attribute that stores the name of the employee holding the specified employee id.
Emp_email: An attribute that stores the email id of the specified employees.
The EMPLOYEE_DETAIL table is given below that will help you understand better:
So from the above table, there are the following Candidate keys:
These are the candidate keys we concluded from the above attributes. Now, we have to choose one primary key, which is the most appropriate out of the three, and it is Emp_Id. So, the primary key is Emp_Id. Now, the remaining two candidate keys are Emp_SSN and Emp_email. Therefore, Emp_SSN and Emp_Email are the alternate keys.
Example 2:
Consider another example of Student_Detail, where we have the following attributes:
Stud_roll_no: An attribute for providing an identification number to the students.
Stud_name: An attribute for storing the names of the students on the specified roll numbers.
Stud_email: An attribute that will hold the email id of the particular student on a specified roll number.
Stud_Course: An attribute that will hold the course taken by the particular student having a specified roll number.
The Student_Detail table is shown below:
From the table, we can conclude that the roll number of the students can be the most appropriate option for setting a Primary Key. Thus, Stud_roll_no is the primary key and the remaining identified keys will be the candidate keys, hence the alternate keys.
So, an alternate key is a candidate key only.