Re: Sequence Numbers as Primary Keys

There are several considerations for using a numeric sequence to replace either a single column or multiple column primary key. Many have already been stated but I did want to point a few more out.

"intelligent" keys will almost always come back to haunt you. Your users will eventually want to modify one (or more) of the columns in your primary key (but we transfered that product to another division so the part "number" has to change!). If this primary key is used as a foreign key in other tables, you have a major mess on your hands: Not only do you have to update the master table, but you must change all of the dependent tables (once you find ALL of them, of course). Depending on the complexity of your table structures, this may not be possible. Or it may have horrible effects on history tables. If the primary key is a system assigned numeric, you will be updating an attribute that is not repeated in any table.

All it takes is doing this once and you will remember the pain and anguish forever.

It is fairly easy to design a SQL*Forms (or other) application to support the concept of a "user key". The user never needs to know that there is a system assigned numeric behind the scenes. I wrote an article that addresses the coding necessary for SQL*Forms 3.0 (which is forwardly portable to 4.0 and 4.5, I believe). If you want a copy, e-mail me. If I get enough responses, I'll post a copy here.

Matt... Received on Thu Sep 28 1995 - 00:00:00 CET