Should sequence numbers be used as primary keys?
What a great question. I really like short questions, and this one's a beaut.
Should sequence numbers be used as primary keys? No, not in all situations.
Can sequence numbers be used as primary keys? Absolutely, yes. There are good reasons for using sequence numbers, or, as they are more generally known, surrogate keys. It depends on the situation.
The debate between natural and surrogate keys is well-known in database circles. This debate always seems to produce strongly stated comments, even inflammatory and personal remarks, if not outright hostility, from participants. For an example, read the comments (there are several hundred, and many of them are brilliant, with some really good ones toward the end) in the following article on the aptly named The Daily WTF Web site:
- A Truly ID-iotic Design
caution: occasional strong language
My advice is: do not use surrogate keys "by rote" on every table you create, but rather, use a surrogate key only if you need to.
Of course, the question then becomes, "When do you not need to?" You do not need to use a surrogate key when there is a perfectly good natural key available.
My best example for this is state/province codes. Consider a table of names and addresses such as the following:
John Brown 123 Sesame Street Springfield 45 USA Joe Smith 456 Oak Avenue Charleston 25 USA Todd Feszcuk 789 Maple Drive Toronto 17 USA
Here you can see that the state is represented by a surrogate key. It is a numeric value, possibly but not necessarily a sequence, used as a foreign key to the state table, in which you will find rows like this:
17 OH Ohio 18 KS Kansas 24 SC South Carolina 25 WV West Virginia 45 MA Massachusetts 47 MO Missouri
The point here is that you cannot tell which state each address belongs to without doing a join to the state table, to look up either the state code or the state name using the surrogate foreign key to get to the right row. In my opinion, using the accepted two-character postal state code as the foreign key is better:
John Brown 123 Sesame Street Springfield MA USA Joe Smith 456 Oak Avenue Charleston WV USA Todd Feszcuk 789 Maple Drive Toronto OH USA
Some people will argue that state code is also a surrogate key, and must also be used as a foreign key to look up the state name. Whether state code is a natural key or a surrogate doesn't really matter, since a lookup join will still be required to get the full state name, if you really need the full state name. But no lookup is needed for you to know which state the state code refers to. No lookup is needed if you're printing addresses on envelopes, since the postal service can recognize the correct state from the two-character state code with ease, and without ambiguity.
And I have yet to meet an "always use a surrogate key" DBA who has actually implemented a surrogate key instead of a state code. It seems there are times when even they will break their own rule.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading