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:
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.
This was first published in September 2005