No, you cannot. Most entities have natural primary keys. People are the exception not the rule. I am not advocating the use of natural keys as the primary keys of tables. I like to sue sequnece numbers for that purpose. However the natural key should be identified and enforced via a unique constraint.
If you only have a sequenced-based primary key how do you protect against duplicate entries? We have that problem with our personnel data because all it has is such a key, and our physics collaborations are world-wide. Different transliterations, switching of first and last names, and individuals without surnames can make life interesting. We have one person who only has a surname. I would think think that must be confusing at home. Perhaps they use a system similar to that in the old joke about the folks in Welsh village: Jones, the baker; and Jones, the post; and Jones the .... We have a program which helps with these problems, but it does not totally prevent someone from being in the database twice for a short time.
I'd hate to think what are database would be link if we didn't enforce natural keys on our other tables.
Ian MacGregor
Stanford Linear Accelerator Cenr
-----Original Message-----
Sent: Wednesday, November 05, 2003 10:34 AM
To: Multiple recipients of list ORACLE-L
I'm fully convinced. SSN should not be used as a PK.
Can we also conclude that natural keys in general are only good if you sit in an ivory tower and do unrealistic lab test?
Yong Huang
- "Bellow, Bambi" <bbellow_at_chi.navtech.com> wrote:
> Having worked for the government in a situation where we were actually
> tracking information BY Social Security Number, let me tell you the
> problems with it.
>
> 1) Social Security Numbers ARE NOT GUARANTEED TO BE UNIQUE
> 2) Individuals ARE NOT GUARANTEED TO HAVE ONE AND ONLY ONE Social
> Security Number
> 3) Not all Social Security Numbers are numeric
> 4) Not all Social Security Numbers which ARE numeric are 9 characters
> in length
> 5) Social Security Numbers can be changed by the holder
> 6) It is illegal to use the Social Security Number for any purpose
> other than that which the government specifically uses Social Security
> Numbers for (ie., the distribution of benefits). I'll bet *that* one
> is strictly enforced.
>
> HTH,
> Bambi.
>
> -----Original Message-----
> Sent: Wednesday, November 05, 2003 8:00 AM
> To: Multiple recipients of list ORACLE-L
>
>
> 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
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: Yong Huang
INET: yong321_at_yahoo.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: MacGregor, Ian A.
INET: ian_at_SLAC.Stanford.EDU
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).
Received on Wed Nov 05 2003 - 13:14:36 CST
