What is the Difference Between Candidate Key and Composite Key

The main difference between candidate key and composite key is that candidate key is a super key with no redundant attributes while the composite key is a key with two or many attributes to identify the rows of the table.

Generally, a database in an RDBMS has tables to store data. The keys help to create a relationship between the tables. Therefore, the keys help to identify the relationship between data stored in various tables. Key can consist of one or multiple attributes (columns).  In overall, there are various types of keys; two of them are candidate key and composite key.

Key Areas Covered

1. What is Candidate Key
      -Definition, Functionality
2. What is Composite Key
      -Definition, Functionality
3. Difference Between Candidate and Composite Key
      -Comparison of key differences

Key Terms

Candidate Key, Composite Key, Primary Key, Super Key

What is Candidate Key

A 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. Moreover, a candidate key should not have any redundant attributes. Generally, a candidate key has no null values. It can also consist of one or more columns or attributes. Furthermore, a table can have one or more candidate keys.

For example, assume a table called student. It has 3 columns which are id, name and phone. Consider the id and phone columns. These do not have redundant attributes. Therefore, these are candidate keys of the student table.

What is Composite Key 

A composite key is a key that contains two or more attributes that help to identify a record in the table uniquely. In other words, these attributes combine together to form a composite key. Therefore, we have to use this combination to identify the records separately. It is not possible to use individual attributes to identify the records.

For example, assume that there is a table to store the marks of the students. It has 4 attributes or columns: student_id, subject_id, marks and exam_name. In this table, we cannot consider either the student_id or the subject_id as the primary key because we cannot uniquely identify the records by using them. However, we can identify each record by using the combination of student_id and subject_id. Therefore, this combination is the primary key. Furthermore, this combination is also a composite key.

Difference Between Candidate Key and Composite Key 

Definition

A candidate key is a super key with no redundant attributes, while a composite key is a key that consists of two or more attributes that uniquely identify any row in the table. Thus, this is the main difference between candidate key and composite key.

Attributes

Moreover, candidate key can have one attribute, while the composite key must have a minimum of two attributes. Hence, this is an important difference between candidate key and composite key.

Examples

In a table with attributes id, name and phone; id and phone are the candidate keys. On the other hand, in a table with attributes student_id, subject_id, marks and exam_name; the composite key is the combination of student_id and subject_id.

Conclusion

In brief, the programmer can use various keys to connect the tables of a database. Two types of keys are candidate key and composite key. The main difference between candidate key and composite key is that candidate key is a super key with no redundant attributes, while the composite key is a key with two or many attributes to identify the rows of the table.

References:

1.“Introduction to Database Keys.” Studytonight, Available here.
2.Singh, Chaitanya, et al. “Candidate Key in DBMS.” Beginnersbook.com, 11 Dec. 2018, Available here.

Image Courtesy:

1.”Relational database terminology” By User:Booyabazooka – Own work (Public Domain) via Commons Wikimedia