Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: How do you genrate primary keys?

RE: How do you genrate primary keys?

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Wed, 05 Nov 2003 14:14:28 -0800
Message-ID: <F001.005D5B15.20031105141428@fatcity.com>


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); Margaret.Tompkins_at_dfas.mil
>

-----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


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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: TOMPKINS, MARGARET
  INET: MARGARET.TOMPKINS_at_DFAS.MIL

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 - 16:14:28 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US