in this particular app, we were registering new users and the pk didn't
need to be returned (email address was a unique field).
- Stefan Jahnke <Stefan.Jahnke_at_bov.de> wrote:
> Hi Rachel
>
> In most Java applications I've seen so far, the issue of caching rows
> by an
> id, which is usually the primary key, arises. JDBC v3 implements a
> method
> which allows you to return a key after the insert completed (for
> example MS
> SQL Server can do this). How do you get a hold of the PK, after you
> inserted
> the key via trigger without an extra roundtrip ?
>
> Regards,
> Stefan
>
> -----Ursprüngliche Nachricht-----
> Von: Rachel Carmichael [mailto:wisernet100_at_yahoo.com]
> Gesendet: Mittwoch, 5. November 2003 14:34
> An: Multiple recipients of list ORACLE-L
> Betreff: Re: How do you genrate primary keys?
>
>
> At one site I worked at, the programmers insisted on using Java
> milliseconds as the primary key -- so that they wouldn't have to hit
> the database twice (once to get the sequence number, once to insert
> the
> row). They swore up, down and six ways from Sunday that there could
> never, ever, EVER be a collision.
>
> After we had collisions in development, we switched to sequences (one
> per table), with a trigger to populate the field on insert so that
> they
> wouldn't have to make the second round-trip.
>
>
> --- Jonathan Gennick <jonathan_at_gennick.com> wrote:
> > 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).
>
>
> __________________________________
> 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: Rachel Carmichael
> INET: wisernet100_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: Stefan Jahnke
> INET: Stefan.Jahnke_at_bov.de
>
> 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: Rachel Carmichael
INET: wisernet100_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).
Received on Mon Nov 10 2003 - 09:44:25 CST
