Yep! Only one Cher. If one already existed, she would have to adopt an alias. Your point is actually very close to reality. I've worked with a "person" table that have a few hundred million rows. We came to realize that it didn't matter how many components you put together - that getting the uniqueness based on a "name" was difficult. We finally abandoned the idea and can have duplicates. The only thing that really makes it work is that these names come attached with other information that is more easily made unique. As long as the foreign keys point to the right rows, all is well. Maggie
Respectfully, > Maggie Tompkins - CAD SQA > Corporate Applications Division > Technology Services Organization - Kansas City > Defense Finance and Accounting Service > 816-926-1117 (DSN 465); [EMAIL PROTECTED] -----Original Message----- Sent: Wednesday, November 05, 2003 4:14 PM To: Multiple recipients of list ORACLE-L So,I tell the other Cher to change her name, or do I not enter her into the database? My point was that personnel data has no natural key and therefore cannot be fully protected from duplicate entries, but that the situation to which this applies are few in number. Ian MacGregor -----Original Message----- Sent: Wednesday, November 05, 2003 11:54 AM To: Multiple recipients of list ORACLE-L For entity uniqueness you have a unique identifier. You might even have more than one. For drawing entity relationship diagrams however, I don't know of any tool that allows you to display more than one, so you have a primary unique identifier and perhaps other unique identifiers that exist but don't show up on an ERD. When the entity gets transformed into a table, each of the unique identifiers should get implemented with a unique key constraint. That is the "natural" unique identifier on the entity becomes a unique key on the table. The table also gets the sequence generated surrogate primary key that we have been talking about. For the names you describe, some people only require one name like Cher and Madonna. If the unique key is made up of several components like first name, last name, etc. then you could have NULL for a last name to accommodate Cher and her friends. That works nicely in a unique key but of course, you can't have NULL as a component of a primary key. However, only one Cher would be allowed in the table. Maggie Respectfully, > Maggie Tompkins - CAD SQA > Corporate Applications Division > Technology Services Organization - Kansas City > Defense Finance and Accounting Service > 816-926-1117 (DSN 465); [EMAIL PROTECTED] > -----Original Message----- Sent: Wednesday, November 05, 2003 1:15 PM To: Multiple recipients of list ORACLE-L 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" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> 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:[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: Mercadante, Thomas F > > INET: [EMAIL PROTECTED] __________________________________ 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: [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: MacGregor, Ian A. 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: TOMPKINS, MARGARET 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: MacGregor, Ian A. 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: TOMPKINS, MARGARET 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).
