Re: Unique ID's question

Jack L. Swayze Sr. (keystrk_at_feist.com) wrote:
> Jesse <keller_at_minerva.cis.yale.edu> wrote:
 

> >Sorry for asking a typical novice question, but...
 

> >Does Oracle7 provide a good way to generate sequential unique ID's, so
> >that each new record gets a unique number for its ID field? If the
> >solution requires pl/sql, can it be invoke via oraperl?

Look into sequences.

> You already have a post that most likely directly answers your
> question. So let me try to convince you not to do this.
> Generating an arbitrary unique identifier should be avoided as it
> will: 1) create a data redundancy that must be managed, 2) hide
> meaning, 3) increase dependency on programming solutions or 4) cause
> the system to be less usfull and perhaps less trusted.
[....]
There are valid cases where it is helpful to have a dumb primary key. For example if there could be a case when the "conceptual" primary key might be changed. Say u have a table of locations in a warehouse. By definition location_name is the primary key of the location table. But the requirements suggest that some times, very rarely though, we might want to change the location_name. Now if location_name is a foreign key into 50 tables; such an update would have to be done for all those 50 tables; but if you had a dumb primary key which was just a sequence, u do not have to worry about updating those 50 tables.

Similarly there might be cases where the "conceptual" primary key comprises 7 or 8 or more columns. If that primary key is a foreign key into several other tables, there is a lot of coding that is required to do the joins; not to mention the additional overhead of parsing 8 rows vs. 1; and of using a larger index vs. a smaller.

Whreever possible, a primary key that is intuitive is the best approch but the sequences are not a useless entity in an oracle database.

--
**************************************************************
*                          Saad Ahmad                        *
*                          Senior Software Engineer          *
*                          McHugh Freeman & Associates, Inc. *
*                          Ph:  (414) 798-7457               *
*                          Fax: (414) 798-8619               *
*                          E-Mail: sahmad_at_mfa.com            *
**************************************************************