| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How do you genrate primary keys?
> I think using a natural key such as Soc. Sec. # as the primary key is
a
> good
> idea.
it is VERY VERY BAD idea.
Oh yea. This is the first thought that these numbers are unique. But the
real life is completely different. Especially where data about people
are involved. So...
We had such a problem in Latvia with population register. Every people
__should__ have a unique Person identifying number that was constructed
from the birth day, month and year and sequence number. Blahhh!! As it
was more than 10 years ago we haven't online databases in every
registration point and as a result we got people with duplicated PiNos.
This problem was actual starting from year ~1992 and ending in ~1998
when I left this state agency. I'm not sure about the situation today
maybe some app that uses his own PKs is created.
So I'm very strong supporter of surrogate PKs and I use them always in all projects that I've managed or participated as an analyst/architect.
And I'v accepted designer standards about three leter abbreviations for tables and use them for sequences for these tables (eg. adr_seq), all column names (eg. adr_city), constraints (eg. adr_pk, adr_uk1, adr_ck1, adr_zon_fk).
Gints
You don't need to maintain the sequence so there's no performance
> issue
> associated with sequences. There's no issue of gaps. No index root
block
> contention. It doesn't seem to be industry common practice though.
>
> In your college student case, changing primary keys is rare so it's
not a
> big
> problem.
>
> Yong Huang
> -----Original Message-----
> From: Yong Huang [mailto:yong321_at_yahoo.com]
> Sent: Wednesday, November 05, 2003 4:00 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: How do you genrate primary keys?
>
> Tom,
>
> I think using a natural key such as Soc. Sec. # as the primary key is
a
> good
> idea. You don't need to maintain the sequence so there's no
performance
> issue
> associated with sequences. There's no issue of gaps. No index root
block
> contention. It doesn't seem to be industry common practice though.
>
> In your college student case, changing primary keys is rare so it's
not a
> big
> problem.
>
> Yong Huang
>
> --- "Mercadante, Thomas F" <NDATFM_at_labor.state.ny.us> wrote:
> > Jonathan,
> >
> > I think your idea of a paper is a good one. But I think we need to
back
> th
> > question up to what the requirements are.
> >
> > First, to me, a primary key should not be something that a user
would
> ever
> > see or use. So the Soc. Sec. # is out. (A side issue - I used to
work
> at a
> > college. Want to know how many times we had to change the Soc. for
an
> > individual student because the parent filled the form out and used
their
> > soc, or the kid used the wrong one?). Any id entered by a user is
> subject
> > to mistakes and changes. So the PK value must be protected from
these
> types
> > of errors.
> >
> > The next requirement that may be needed is sequentiallity (is this a
> word?).
> > Does the application require that every sequence number be used.
> Sometimes
> > the answer is yes, and sometimes it just doesn't matter.
> >
> > These are the only two requirements I can think of. Based on the
> answers,
> > we then have options. Right now, Oracle sequences are working well
for
> me.
> > I like the idea of SYS_GUID, just not sure where I would need it.
> >
> > Good idea and good luck!
> >
> > Tom Mercadante
> > Oracle Certified Professional
> >
> >
> > -----Original Message-----
> > Sent: Wednesday, November 05, 2003 8:19 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > The recent article that mentioned sequences got me to
> > thinking. I might pitch a more detailed article on sequences
> > to Builder.com. But a more interesting article might be one
> > that explored various ways to automatically generate primary
> > keys. So, in the name of research, let me throw out the
> > following questions:
> >
> > What mechanisms have you used to generate primary keys?
> > Which ones worked well, and why? Which mechanisms worked
> > poorly?
> >
> > I've run up against the following approaches:
> >
> > * Hit a table that keeps a counter. This is the "roll your
> > own sequence method". The one time I recall encountering
> > this approach, I helped convert it over to using stored
> > sequences. This was because of concurrency problems: with
> > careful timing, two users could end up with the same ID
> > number for different records. Is there ever a case when this
> > roll-your-own approach makes sense, and is workable?
> >
> > * Stored sequences. I worked on one app that used a separate
> > sequence for each automatically generated primary key. I
> > worked on another app, a smaller one, that used the same
> > sequence for more than one table. The only issue that I
> > recall is that sometimes numbers would be skipped. But end
> > users really didn't care, or even notice.
> >
> > * The SYS_GUID approach. I've never used SYS_GUID as a
> > primary key generator. I wonder, was that Oracle's
> > motivation for creating the function? Has anyone used it for
> > primary keys in a production app? What's the real reason
> > Oracle created this function?
> >
> > * Similar to SYS_GUID, I once worked on an obituary-tracking
> > application that built up a primary key from, as best I can
> > recall now: date of death, part of surname, part of first
> > name, and a sequence number used only to resolve collisions,
> > of which there were few. The approached worked well,
> > actually, because whatever fields we munged together to
> > generate a primary key gave us a unique key the vast
> > majority of the time.
> >
> > The SYS_GUID approach is interesting, but if you need an ID
> > number that users will see, and that users might type in
> > themselves (e.g. social security number), is SYS_GUID really
> > all that viable?
> >
> > Best regards,
> >
> > Jonathan Gennick --- Brighten the corner where you are
> > http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com
> >
> > Join the Oracle-article list and receive one
> > article on Oracle technologies per month by
> > email. To join, visit
> > http://four.pairlist.net/mailman/listinfo/oracle-article,
> > or send email to Oracle-article-request_at_gennick.com and
> > include the word "subscribe" in either the subject or body.
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Jonathan Gennick
> > INET: jonathan_at_gennick.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting
services
> >
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gints Plivna INET: g.plivna_at_softex.lv Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Nov 05 2003 - 08:29:25 CST
 |
 |