Problem solve Get help with specific problems with your technologies, process and projects.

Candidate keys

What is a candidate key in SQL?

What is a candidate key in SQL?

A candidate key is any column, or set of columns, which has unique values. How many candidate keys can you find in the following data?

 id  stooge  name       birthdate
 23    3     curly      1903-10-22
 25    2     larry      1902-10-05
 11    1     moe        1897-06-19
 15    3     shemp      1895-03-17
  9    3     joe        1907-08-12
 37    3     curly joe  1909-07-12

That's right, there are 14 candidate keys:

  • id
  • id,stooge
  • id,stooge,name
  • id,stooge,name,birthdate
  • id,stooge,birthdate
  • id,name
  • id,name,birthdate
  • id,birthdate
  • stooge,name
  • stooge,name,birthdate
  • stooge,birthdate
  • name
  • name,birthdate
  • birthdate

The only one that's missing from all the permutations and combinations is the key consisting of the stooge column alone, which cannot be a candidate key because it isn't unique. Feel free to check all the others—they are all unique.

Now as you may know, any of the candidate keys in a table can be designated as the primary key. Which candidate key(s) here would make a good primary key?

Birthdate by itself would not make a good primary key, because this would mean that we could never add someone to the table who has the same birthdate as anyone already in the table. (Actually, this wouldn't matter for the stooges, of which there were only those six.) Name and birthdate together might work, until we realize that it's possible to have two people with the same name born on the same day. And so on. Eventually we might eliminate most of the candidate keys this way.

The id column by itself would make a good primary key, if we could generate the numbers in a way that guaranteed that they would all be unique forever. This is, in fact, the most attractive feature of an autonumber (also known as an auto_increment or identity or sequence or serial number). It provides a set of ever increasing numbers, each of which is, by definition, unique.

But let us not be lulled into a false sense of security. Just because we've declared the autonumber id as our primary key does not solve all our potential problems. If the primary key is all we've declared, then it is always possible that we could add the same person twice, with the same stooge number, same name, same birthdate:

 id  stooge  name       birthdate
 23    3     curly      1903-10-22
 25    2     larry      1902-10-05
 11    1     moe        1897-06-19
 15    3     shemp      1895-03-17
  9    3     joe        1907-08-12
 37    3     curly joe  1909-07-12
 29    2     larry      1902-10-05

So how do we prevent this? By also declaring one or more unique constraints on those column(s) or sets of columns which we want never to have duplicates. In other words, we declare unique constraints on selected candidate keys.

Dig Deeper on Oracle and SQL

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.