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.