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
| [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED] 11/06/2003 12:19 AM
|
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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
Jo
Jonathan Gennick <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
11/05/2003 14:19
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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:[EMAIL PROTECTED]
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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
