Q

Should sequence numbers be used as primary keys?

Should sequence numbers be used as primary keys?

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:

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.

This was first published in December 2005

Dig deeper on Oracle and SQL

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close