Re: oracle sequence numbers

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.

Bob Hairgrove
rhairgroveNoSpam_at_Pleasebigfoot.com Received on Wed Jan 15 2003 - 13:12:21 CST