[WEBINAR] Bulletproof: How Today's Business Leaders Stay on Top

Candidate Key

Definition - What does Candidate Key mean?

A candidate key is a column, or set of columns, in a table that can uniquely identify any database record without referring to any other data. Each table may have one or more candidate keys, but one candidate key is unique, and it is called the primary key. This is usually the best among the candidate keys to use for identification.

When a key is composed of more than one column, it is known as a composite key.

Techopedia explains Candidate Key

The best way to define candidate keys is with an example. For example, a bank’s database is being designed. To uniquely define each customer’s account, a combination of the customer’s birth date and a sequential number for each of his or her accounts can be used. So, Mr. Andrew Smith’s checking account can be numbered 120344-1, and his savings account 120344-2. A candidate key has just been created.

This can raise problems. What if more than one person with the same birth date wants to open an account with the bank?

Because of such potential pitfalls, a frequently used option is to create a unique 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 birth dates and a sequential number, 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.

Share this:

Connect with us

Email Newsletter

Join thousands of others with our weekly newsletter

The 4th Era of IT Infrastructure: Superconverged Systems
The 4th Era of IT Infrastructure: Superconverged Systems:
Learn the benefits and limitations of the 3 generations of IT infrastructure – siloed, converged and hyperconverged – and discover how the 4th...
Approaches and Benefits of Network Virtualization
Approaches and Benefits of Network Virtualization:
Businesses today aspire to achieve a software-defined datacenter (SDDC) to enhance business agility and reduce operational complexity. However, the...
Free E-Book: Public Cloud Guide
Free E-Book: Public Cloud Guide:
This white paper is for leaders of Operations, Engineering, or Infrastructure teams who are creating or executing an IT roadmap.
Free Tool: Virtual Health Monitor
Free Tool: Virtual Health Monitor:
Virtual Health Monitor is a free virtualization monitoring and reporting tool for VMware, Hyper-V, RHEV, and XenServer environments.
Free 30 Day Trial – Turbonomic
Free 30 Day Trial – Turbonomic:
Turbonomic delivers an autonomic platform where virtual and cloud environments self-manage in real-time to assure application performance.