Q

Candidate key explained

Can you explain what a candidate key is, in simple terms?

A Candidate Key (CK) is a column or group of columns that uniquely describe every row in a table.

For instance, in a table that describes people (employees, customers, club members, whatever), there are many columns that describe those people. If you accept the assertion that you can't have two people with the same name (which is a VERY bad idea in my experience), one of your CKs could be the person's name. If you accept the assertion that you can't have two people with a given phone number (again, a VERY bad idea in my experience), one of your CKs could be the phone number. Especially with people, just about every CK I can think of is subject to exceptions.

A Surrogate Key (SK) is an arbitrary value that is used solely by the database (and therefore by any programs that use that database) to uniquely identify a row. This can be a GUID, an "automagically" assigned number, or just some arbitrary value that happens to be unique for every row. SKs are guaranteed to be unique (that is part of their definition), so they are always CKs. Using an SK can be extremely convenient because it allows the database to function independently of any changes to the business rules that make other CKs unique.

For More Information


This was first published in September 2002

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close