Oracle-L: Re: How do you genrate primary keys?

From: <Jared.Still_at_radisys.com>
Date: Thu, 06 Nov 2003 12:49:31 -0800
Message-ID: <F001.005D5DB3.20031106124931@fatcity.com>

One thing missed in that article is that the use of natural keys as foreign keys
does not follow the relational model closely, and presents an update anomoly.

eg. if you update the natural key in the parent, you must also update the natural
key everywhere it appears as an fk.

This gets really ugly when you think about making modifications to the schema.

Jared

jo_holvoet_at_amis.com
Sent by: ml-errors_at_fatcity.com
11/06/2003 12:19 AM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Re: How do you genrate primary keys?

I'm a bit surprised no one's mentioned it, but there's an article about the use of surrogate keys at Ixora :

http://www.ixora.com.au/tips/design/synthetic_keys.htm

mvg/regards

Jonathan Gennick <jonathan_at_gennick.com>
Sent by: ml-errors_at_fatcity.com
11/05/2003 14:19
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

        cc: 
        Subject:        How do you genrate primary keys?

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:
INET: jo_holvoet_at_amis.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author:
INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------