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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Inserting a new PK into an existing table

Re: Inserting a new PK into an existing table

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 31 Aug 2006 11:31:29 -0700
Message-ID: <1157049089.557809@bubbleator.drizzle.com>


David Newman wrote:
> "Mark D Powell" <Mark.Powell_at_eds.com> writes:
>

>> Jens Lenge wrote:
>>
>> Normally we use a business column or set of column values in the table
>> to be the PK and do not use an artificial key since if a unique
>> business value exists there is no need for or real use of an artificial
>> key.
>>

>
> This is an interesting subject to me. Normally I would fully agree with
> you. But I have been learning the Hibernate persistence library lately
> and they refer to this type of model, one in which actual business
> columns are used in the primary key, as "legacy" and not designed well
> and that an artificial primary key is always preferred. I've always
> felt that if you are going to have something in the table, like an
> employee id or SSN, that is going to have a unique constraint on it anyway
> you might as well make it the primary key. Is this some new way of
> thinking?
>
> --
> Dave Newman

Java is sustained by people whose knowledge of relational databases is at about the level at which philosophy is taught to 4th graders.

Essentially you have a choice between natural keys and surrogate keys. If you use a natural key, for example a tax identification number, it must conform to the rules for a primary key in the database in which you are working. If there is no natural key, and I would argue ONLY if there is no natural key, would I resort to using a surrogate key which is usually an incremented integer (in Oracle generated by a sequence object). One of the biggest problems with surrogate keys is that they are incapable, without natural keys, of eliminating duplicates in column other than their own.

I would give the opinions of those coming from Hibernate all the time it deserves: None!

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Thu Aug 31 2006 - 13:31:29 CDT

Original text of this message

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