top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

What is a candidate key?

+1 vote
What is a candidate key?
posted Mar 25, 2014 by Vishvachi Tiwari

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

+2 votes

A table may have more than one combination of columns that could uniquely identify the rows in a table; each combination is a candidate key.

During database design you can pick up one of the candidate keys to be the primary key.

For example, in the supplier table supplierid and suppliername can be candidate key but you will only pick up supplierid as the primary key.

answer Mar 25, 2014 by Atul Mishra
+1 vote

A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table (where as there can be only one primary key). Each Candidate Key can qualify as Primary Key.

Example from Techopedia:
A bank’s database is being designed. To uniquely define each customer’s account, a combination of the customer’s ID or social security number (SSN) and a sequential number for each of his or her accounts can be used. So, Mr. Andrew Smith’s checking account can be numbered 223344-1, and his savings account 223344-2. A candidate key has just been created.

This can raise problems. What if the government makes a mistake and issues the same SSN to more than one person, and both now want to open an account with the bank?

Because of such potential pitfalls, a frequently used option is to create your own candidate key. In this case, the bank’s database can issue unique account numbers that are guaranteed to prevent the problem just highlighted. For good measure, these account numbers can have some built-in logic. For example checking accounts can begin with a ‘C,’ followed by the year and month of creation, and within that month, a sequential number. So Andrew Smith’s checking account can now be C-200805-22. Even without referring elsewhere, a teller can identify that this was the 22nd checking account created in May 2008. Savings accounts follow the same logic, but with an ‘S’ instead of ’C.’

Note that it was possible to uniquely identify each account using the aforementioned SSNs and a sequential number (assuming no government mess-up, in which the same number is issued to two people). So, this is a candidate key that can potentially be used to identify records. However, a much better way of doing the same thing has just been demonstrated - creating a candidate key. In fact, if the chosen candidate key is so good that it can certainly uniquely identify each and every record, then it should be used as the primary key. All databases allow the definition of one, and only one, primary key per table.

answer Mar 26, 2014 by Salil Agrawal