Secondary Key in DBMS
What are Keys?
Keys in DBMS are the attributes or set of attributes that uniquely identify a row in a set of relations. A table (relation) can have a column or multiple columns. Key helps establish a relation between the tables with the help of the primary Key, and the keys that have not been selected as candidate keys are called Secondary keys in DBMS. A candidate key is a key that can be considered to be a primary key.
What is the Secondary Key?
The secondary Key is nothing but the Key which was not chosen to be the primary Key. From the set of candidate keys, one Key is chosen to be the primary Key, and others are called secondary Keys. They are also called alternate Keys as they uniquely identify a row in a table.
Thus, an unselected candidate key used as a primary key is called a secondary key. The secondary is not a foreign key; it can be zero, one, or more than one.
What is the Difference Between a Primary Key And a Secondary Key in DBMS?
In DBMS, the main difference between a primary key and a secondary key is that the user chooses the primary Key to identify each row uniquely. In contrast, the candidate keys not selected for the primary Key are called secondary keys.
Secondary keys can become the primary Key as it is perfectly capable of uniquely identifying rows in the table.
| Basis | Primary Key | Secondary Key |
| Definition | The Key, which is unique and is selected by the database administrator to help uniquely identify a row in a database, is called the primary Key. | The Key that uniquely identifies a row but is not selected as the primary Key is known as a secondary or alternate Key. |
| NULL values | It cannot be NULL. | It can be NULL. |
| Number of keys | There can be only and only one primary Key in a table | There can be zero, one, or more secondary keys. |
| Adm_ID | Roll_no | Name | Age | |
| 3655 | 2192309 | Aaradhya | 20 | [email protected] |
| 3271 | 2192312 | Sameer | 21 | [email protected] |
| 3108 | 2192315 | Sanya | 19 | [email protected] |
| 3321 | 2192320 | Chelsea | 20 | [email protected] |
Example:
From the above-given table, it can be concluded that Adm_ID, Roll_no, and Email are the candidate keys. They are considered candidate keys because they can be used to identify the student record uniquely. We can select any candidate key as the Primary Key, and the rest of the two would become the Secondary Key.
The Adm_id has more chances to become the primary Key as it will be more convenient to find and establish relations with other tables with the help of the admission id.
Example-
Let us take another example of employee information:
| Emp_ID | Emp_DOJ | Emp_Name | Emp_Email | Emp_Dept |
| 1001 | 12/07/2012 | Rakesh | [email protected] | Finance |
| 1002 | 07/03/2016 | Neetu | [email protected] | HR |
| 1003 | 21/11/2010 | Samiksha | [email protected] | Sales manager |
| 1004 | 19/05/2020 | Abhishek | [email protected] | Web developer |
From the above table on employee information, we can observe that Emp_ID and Employee_Email have the potential to be the candidate keys. They can help to identify the Employee record uniquely. We can select any candidate key as the Primary Key, and the rest of the two would become the Secondary Key. The primary Key would be Emp_id, as it can be convenient to establish and find relations between other tables.