What is a Secondary Key in DBMS?
FREE
Keys in DBMS are attributes or sets of attributes that help uniquely identify rows in a table. A table can have multiple columns or a set of columns that uniquely identify the rows. Now all the columns or sets of columns that uniquely identify the rows are called the candidate keys. The set of candidate keys is called super keys. Out of all the candidate keys, one is chosen as the primary key, and the rest of the candidate keys that have not been chosen to be candidate keys are called secondary keys in DBMS.
In other words, Secondary key in DBMS is a column or a set of columns in a table that uniquely identifies each row in a table that is not the primary key. Secondary keys are also called alternate keys as they can also be used alternatively in place of the primary key.
Examples
Look at the example below to understand secondary keys.
| Class_ID | Student_ID | Enrollment_NO | Name | Course_ID |
|---|---|---|---|---|
| AB | 23 | 5683 | Manish | SN243 |
| AC | 42 | 5628 | Shreya | DF412 |
| AC | 23 | 5629 | Kartik | SN243 |
| AB | 41 | 5610 | Rajesh | DF901 |
| AB | 78 | 5719 | Manan | YU431 |
| AB | 46 | 5412 | Disha | YU431 |
| AC | 46 | 5513 | Tina | OP994 |
In the above table, we can uniquely identify students by their enrollment numbers. Class ID along with student ID can also uniquely identify the students as no two students in a class are going to have the same student ID. Thus, Enrollment_NO and Class_ID + Student_ID are the two candidate keys as both of them can be used as the primary key to identify the rows uniquely for valid student data.
So, we can select one of them as the primary key let's say, Enrollment_NO is chosen as the primary key then, Class_ ID + Student_ID is said to be the secondary key.
What is the Difference Between a Primary Key And a Secondary Key in DBMS?
The key difference between a primary key and a secondary key in DBMS is, the primary key is chosen by the administrator for identifying each row uniquely whereas, candidate keys that are not selected for the primary key are called secondary keys.
Secondary keys have all the properties of the primary key, it is perfectly capable of determining tuples uniquely in the table.
| Basis | Primary Key | Secondary Key |
|---|---|---|
| Definition | A key that is unique, not null, and is selected by the database administrator to uniquely identify tuples is called the primary key. | A key that uniquely identifies rows but is not selected as the primary key is known as a secondary key or alternate key. |
| NULL values | It cannot be NULL. | It can be NULL. |
| Number of keys | There must be one and only one primary key. | There can be zero or more secondary keys. |
Conclusion
- Secondary keys are also called alternate keys. They uniquely identify rows in a table and thus can be used alternatively as primary keys.
- From the set of candidate keys, only one primary key is chosen by the database administrator, and the rest of the candidate keys are called secondary keys.
- There is only one primary key but there can be no secondary key or it can be more than one.