RE: How do you genrate primary keys?

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Mon, 10 Nov 2003 08:24:25 -0800
Message-ID: <F001.005D63A0.20031110082425@fatcity.com>

Oracle has "RETURNING" clause for insert.

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

-----Original Message-----
Stefan Jahnke
Sent: Monday, November 10, 2003 10:34 AM To: Multiple recipients of list ORACLE-L

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).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Igor Neyman
  INET: ineyman_at_perceptron.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 - 10:24:25 CST