The main difference between primary key and candidate key is that primary key is the minimal set of attributes of the table that helps to identify each row uniquely, while candidate key is a super key with no redundant attributes.
Generally, a database of an RDBMS consists of multiple tables. Therefore, keys help to establish the relationships between tables. Moreover, a key can consist of one attribute or a combination of multiple attributes. In overall, there are various keys, and these keys help to identify any row of the data in a table. Two of them are primary key and candidate key.
Key Areas Covered
Candidate Key, Primary Key, Super Key, Table
What is Primary Key
A primary key is a candidate key. It is considered as the main key for any table. It helps to uniquely identify each row or a record of the table. For example, assume a student table. It has four properties called id, name, age and phone. But, we cannot consider the name as the primary key because there can be students with the same name. Similarly, it is not a good idea to select age as the primary key because multiple students can have the same age. However, it is possible to use id as the primary key because each student has a unique id. Likewise, we can use only the id to identify each row. Therefore, this is the primary key of the table.
What is Candidate Key
Super key is a set of one or multiple attributes which can uniquely identify a record in a table. A candidate key is a key selected from the set of super keys. Furthermore, the candidate key should not have any redundant attributes.
Moreover, the candidate keys have several properties. First, the candidate key can never be null or empty. Secondly, it can be a combination of more than one columns (attributes). Finally, there can be more than one candidate keys for a table. So, if we look at the student table we discussed in the primary key section again, the id and phone do not have redundant attributes. Therefore, these are the candidate keys for the table student.
Difference Between Primary Key and Candidate Key
The primary key is a minimal set of attributes (columns) in a table that uniquely identifies tuples (rows) in that table, while the candidate key is a super key with no redundant attributes.
For example, in a student table with id, name, age and phone, the id is a primary key, while id and phone are candidate keys of that table.
In RDBMS, tables with relationships provide more convenient and meaningful data. In this, a developer can use keys to represent the relationship among tables. Thus, there are various keys in RDBMS. And, two types of keys are primary key and candidate key. In brief, the main difference between primary key and candidate key is that the primary key is the minimal set of attributes of the table that helps to identify each row uniquely, while a candidate key is a super key with no redundant attributes.
1.“Introduction to Database Keys.” Studytonight, Available here.
2.Singh, Chaitanya. “Primary Key in DBMS.” Beginnersbook.com, 11 Dec. 2018, Available here.
3.Singh, Chaitanya, et al. “Super Key in DBMS.” Beginnersbook.com, 11 Dec. 2018, Available here.
4.Singh, Chaitanya, et al. “Candidate Key in DBMS.” Beginnersbook.com, 11 Dec. 2018, Available here.