Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

dbms
unique
identifier
communitycreator

What is a candidate key in DBMS?

NDUKWE CHIDERA K.

A database management system (DBMS) is software that organizes everything that concerns a database. It provides us with a database, manages the insert, update, delete, read, and various other operations performed on the database.

Some common DBMS are MySQL, Oracle, Postgres, and MongoDB.

Unique identifiers

When setting up an SQL database table/relation, there is a need to add a unique identifier to each row of the table. This unique identifier is known as a primary key.

A primary key uniquely identifies every row in a relation. These identifiers are seen as unique because no two rows in a table can have the same identifier.

student_roster

student_id

name

class

1

Smith Whyte

HumanitiesG7

2

Viktor Monday

ScienceG5

3

John Doe

HumanitiesG7

4

Janet Joe

ScienceG5

In the table above, we can easily choose student_id as the primary key. This is because amongst every attribute or column in the relation, only the student_id column has the possibility of never belonging to more than one student. Two or more students can bear exactly the same name, and can be in the same class, but can’t have the same id.

Choosing the primary key in a relation like the one above is considerably easy, but the same cannot be said of a table where we have a pool of fully qualified unique identifiers which we can select from. This brings us to the concept of candidate keys.

Candidate keys

A candidate key is a set of single-column unique identifiers that can, on their own, uniquely identify the tuples of a table.

They are single column in that they are not made by combining multiple columns. From this set of candidate keys, a primary key can be chosen, and doing this can prove difficult. Candidate keys are a subset of the super key superset.

Example

Some examples will come in handy here. In the table below, the Customers relation has several keys that can be used to uniquely identify the table rows.

Customers

c_id

name

phone

email

receipt_no

1

Becky Queens

59994889

becky@example.com

4543g

2

Timothy fosah

92989303

fosah@another.com

8479b

2

Allan Peter

84993030

allan@email.com

7649y

From the table above, there is a host of attributes from the Customer relation table that can be used as the primary key. This list of eligible attributes forms the candidate key set. From the table above, the candidate keys are:

The candidate keys

Candidates

c_id

phone

email

receipt_no

Things to note

Some things to point out here are as follows:

  • In a properly normalized table, the receipt_no attribute won’t be in that table. Instead it should be from, e.g., a Receipts table, and then its primary key can be placed as a foreign key in the Customers table.

  • Some Database systems automatically choose the auto-increment column as the primary key. If you have some other attribute in mind to use rather than the auto-increment attribute, then avoid having an auto-increment attribute.

RELATED TAGS

dbms
unique
identifier
communitycreator
RELATED COURSES

View all Courses

Keep Exploring