Re: oracle sequence numbers

From: Joel Meulenberg <my_first_and_last_name_with_no_punctuation_at_attbi.com>
Date: Thu, 16 Jan 2003 07:14:21 GMT
Message-ID: <3E265BEF.3080204@attbi.com>

Bob Hairgrove wrote:
> On Tue, 14 Jan 2003 23:33:11 +0000, helen_fearman
> <member22597_at_dbforums.com> wrote:
>

>>What are the main pros and cons of using oracle sequence numbers for
>>primary keys in situations where good unique candidate keys exist and
>>which could be designated as primary keys?  Is there an accepted "best
>>practice"?

>
> If you do use a sequence as PK, it is absolutely necessary to define a
> unique index on a column or group of columns which comprise a
> candidate key. The mere *existence* of a surrogate key such as that
> generated by a sequence will guarantee that the row is unique,
> although it might not be if only all the other columns are considered
> separately.
>
> "Best practice"? If you do have a "good unique candidate key", then
> use that instead of the sequence. I would only use a sequence if there
> were no other means of generating a unique identifier ... IOW, as a
> last resort. Unfortunately, this situation occurs fairly often in
> real-life applications.
>
> You can also use a timestamp instead of a sequence ... at least this
> has the advantage of letting you know when the data was inserted.

I thought I'd add a word of caution and expand on the suggestion of using surrogate keys only as a "last resort".

I would not recommend using a timestamp as a surrogate key. Just think about what happens when you want to insert two rows within the same second (or whatever the smallest grain of time is for your DBMS (or even your own homegrown time datatype for that matter)).

Desirable qualities for primary keys (beyond the requirements placed on all candidate keys) include:

• Simplicity (Fewer key columns is simpler.)
• Stability (The key value changes little if at all.)
• Familiarity (People already know the key values.)

If you've got a candidate key with these desirable qualities, then, by all means, use it as a primary key. Otherwise, a surrogate key may be used for the sake of simplicity or stability. By definition, a surrogate key will not be familiar to anyone. So surrogate keys do not help with the familiarity quality. (Though if the pre-existing candidate keys are not already familiar to people, then surrogate keys don't hurt either.)

Finally, whatever gains you expect in simplicity and stability should be weighed against the added complexity that comes with introducing a surrogate key. You've added another column. You've probably introduced a new sequence generator of some kind. You've probably got a little more code to write for inserting rows. Etc.

+Joel Received on Thu Jan 16 2003 - 01:14:21 CST