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: Nuno Pinto do Souto <>
Date: Wed, 05 Nov 2003 12:44:26 -0800
Message-ID: <>

 ('binary' encoding is not supported, stored as-is)

> Jonathan Gennick <> wrote:
> What mechanisms have you used to generate primary keys?
> Which ones worked well, and why? Which mechanisms worked
> poorly?

The "roll your own table" thing is just about as bad as I've ever had and not really THAT portable. The serialisation problem is the same for ALL database architectures, you have to do non-portable tricks to solve the problem such as multiple rows for multiple ranges, each row in its block, or partition. Silly, really.

GUID is too heavy on space. Just too long a key for practical purposes. And I don't care how cheap disks have become!

I've used in the past things like Julian days + year + seconds in day + session number as "seeds" for GUIDs, but they all have potential problems.

Now, I use mostly sequences, but with a twist. Surrogate keys almost exclusively. Because I work in environments that may require M-M replication later on, I have to generate globally unique keys. So, I always generate an "instance" table that stores the database name and a number associated to it, between 0 and 1000. This number is then SUFFIXED to all sequence -generated surrogate keys everywhere.

This ensures globally unique keys at very low cost in processing and space overhead. And it will work in RAC as well! I got the idea from some replies in asktom.

Nuno Souto

Please see the official ORACLE-L FAQ:
Author: Nuno Pinto do Souto

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 Wed Nov 05 2003 - 14:44:26 CST

Original text of this message