Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: How do you genrate primary keys?

From: Vladimir Begun <>
Date: Fri, 07 Nov 2003 12:54:38 -0800
Message-ID: <>


Yet another way [I do understand the drawbacks :)]:

For example, the caller knows that it would need 10 unique numbers for the given transaction:

  1. get_next_range('COMMON_SEQUENCE', 10); -> 17 (if someone else call get_next_range now he would get 27)
  2. INSERT INTO table_a (id, name) VALUES(get_next('COMMON_SEQUENCE', 'test'); etc.

Practically it does emulate regular sequences but it allows each caller to define its own pool, that could be a bit more efficient but requires some programming and accuracy.

Also, one can consider something like:

CHR(65 + MOD(SYS_CONTEXT('USERENV', 'SESSIONID'), N)) add this prefix and make primary keys e.g. character based (or play around and stick with numeric values only) using the approach described above. It would make particular sessions based on particular sequence entry of my_sequences table.

Gaps are unavoidable here.

*Drawbacks* are *clear*, so it's just an idea that can be implemented and tested. I'm pretty sure that the approach is disputable, so please let's avoid long discussions.

Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

Jonathan Gennick wrote:

> The recent article that mentioned sequences got me to
> thinking. I might pitch a more detailed article on sequences
> to 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,
-- Please see the official ORACLE-L FAQ: -- Author: Vladimir Begun INET: Fat City Network Services -- 858-538-5051 San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: (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 Fri Nov 07 2003 - 14:54:38 CST

Original text of this message