Re: How do you genrate primary keys?

Yong, sorry but they are federal law prohibiting using SSN as a key, so the point is moot.

joe

Yong Huang wrote:

>Tom,
>
>I think using a natural key such as Soc. Sec. # as the primary key is a good
>idea. You don't need to maintain the sequence so there's no performance issue
>associated with sequences. There's no issue of gaps. No index root block
>contention. It doesn't seem to be industry common practice though.
>
>In your college student case, changing primary keys is rare so it's not a big
>problem.
>
>Yong Huang
>
>--- "Mercadante, Thomas F" <NDATFM_at_labor.state.ny.us> wrote:
>
>
>>Jonathan,
>>
>>I think your idea of a paper is a good one. But I think we need to back th
>>question up to what the requirements are.
>>
>>First, to me, a primary key should not be something that a user would ever
>>see or use. So the Soc. Sec. # is out. (A side issue - I used to work at a
>>college. Want to know how many times we had to change the Soc. for an
>>individual student because the parent filled the form out and used their
>>soc, or the kid used the wrong one?). Any id entered by a user is subject
>>to mistakes and changes. So the PK value must be protected from these types
>>of errors.
>>
>>The next requirement that may be needed is sequentiallity (is this a word?).
>>Does the application require that every sequence number be used. Sometimes
>>the answer is yes, and sometimes it just doesn't matter.
>>
>>These are the only two requirements I can think of. Based on the answers,
>>we then have options. Right now, Oracle sequences are working well for me.
>>I like the idea of SYS_GUID, just not sure where I would need it.
>>
>>Good idea and good luck!
>>
>>Tom Mercadante
>>Oracle Certified Professional
>>
>>
>>-----Original Message-----
>>Sent: Wednesday, November 05, 2003 8:19 AM
>>To: Multiple recipients of list ORACLE-L
>>
>>
>>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: Mercadante, Thomas F
>> INET: NDATFM_at_labor.state.ny.us
>>
>>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: Joe Testa
  INET: oracle-l_at_oracle-dba.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).