Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: How do you genrate primary keys?

RE: How do you genrate primary keys?

From: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: Thu, 06 Nov 2003 12:39:34 -0800
Message-ID: <F001.005D5DAF.20031106123934@fatcity.com>


What I mean't was, and maybe this is different in different countries, if we have an employee who leaves us, realizes just what a nice guy I was to work with :(, and returns then one of two things will happen. If they return in a different tax year, they get the same empno, but for a different employment. If they return in the same tax year they get a different empno, for tax accounting reasons. Thus the same employee can reuse a key for a different logical employment, or have a different key for a different employment. The business rules mean that they are bad keys

Niall
> -----Original Message-----
> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On
> Behalf Of TOMPKINS, MARGARET
> Sent: 05 November 2003 21:25
> To: Multiple recipients of list ORACLE-L
> Subject: RE: How do you genrate primary keys?
>
>
> The thought is that if it is "internal" then you control it.
> Of course, it doesn't mean you will do it right. ;-) Maggie
>
> -----Original Message-----
> Sent: Wednesday, November 05, 2003 3:05 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Except of course that internal employee ids also can get
> reused, and the converse the same individual can have more
> than one employee id.
>
> Niall
>
> > -----Original Message-----
> > From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On
> > Behalf Of TOMPKINS, MARGARET
> > Sent: 05 November 2003 14:10
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: How do you genrate primary keys?
> >
> >
> > Social security numbers are notoriously bad natural primary
> > keys. Did you know that they are re-used? Yes, it's true.
> > Generally, they don't get re-issued until after one of the
> > users dies, but it's been a problem in the past and still is.
> > What do you do with people who don't have SSNs? Foreign
> > nationals and others that work for US companies oversees or
> > provide goods/services generally do NOT have SSNs. An
> > internal employee id would be a much better choice if a
> > "natural" primary key is needed.
> >
> > Respectfully,
> > > Maggie Tompkins - CAD SQA
> > > Corporate Applications Division
> > > Technology Services Organization - Kansas City
> > > Defense Finance and Accounting Service
> > > 816-926-1117 (DSN 465); Margaret.Tompkins_at_dfas.mil
> > >
> >
> >
> > -----Original Message-----
> > Sent: Wednesday, November 05, 2003 8:00 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > 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
> > >
> >
> ---------------------------------------------------------------------
> > > 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).
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: Mercadante, Thomas F
> > > INET: NDATFM_at_labor.state.ny.us
> > >
> > > 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).
> >
> >
> > __________________________________
> > Do you Yahoo!?
> > Protect your identity with Yahoo! Mail AddressGuard
> > http://antispam.yahoo.com/whatsnewfree
> > --
> > Please see the
> > official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Yong Huang
> > INET: yong321_at_yahoo.com
> >
> > 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).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: TOMPKINS, MARGARET
> > INET: MARGARET.TOMPKINS_at_DFAS.MIL
> >
> > 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).
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Niall Litchfield
> INET: niall.litchfield_at_dial.pipex.com
>
> 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: TOMPKINS, MARGARET
> INET: MARGARET.TOMPKINS_at_DFAS.MIL
>
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Niall Litchfield
  INET: niall.litchfield_at_dial.pipex.com

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 Thu Nov 06 2003 - 14:39:34 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US