Re: Inserting a new PK into an existing table

David Newman wrote:
"Mark D Powell" <Mark.Powell@xxxxxxx> 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@xxxxxxxxxxxxxxxx
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.

Relevant Pages

  • Re: How should I generate a primary key?
    ... And I have agreed that there are cases where a pseudokey is useful. ... the business case that it should be VERY HARD to change ... change the value of the primary key which is more problematic in the field ... The generated key is used as a primary key, but the natural key still sits ...
    (comp.databases)
  • Re: Inserting a new PK into an existing table
    ... David Newman wrote: ... Normally we use a business column or set of column values in the table ... and that an artificial primary key is always preferred. ... Agree with natual/surrogate post by Daniel - except, SSN is not ...
    (comp.databases.oracle.misc)
  • Re: How should I generate a primary key?
    ... Obviously the designer did not get all of the business rules from the ... mandatory and hence putting that field in a primary key is invalid whilst at ... When you have a stores application of any kind, ... it is a more flexible design ...
    (comp.databases)
  • Re: Migrating an Application from SQL Server 2000 to Oracle 10g
    ... Puget Sound Oracle Users Group ... If you're ABSOLUTELY sure the natural key never changes ... Business processes can change in an unpredictable way. ... the cost of that decision necessarily had to include the cost of ...
    (comp.databases.oracle.server)
  • Re: Migrating an Application from SQL Server 2000 to Oracle 10g
    ... Puget Sound Oracle Users Group ... If you're ABSOLUTELY sure the natural key never changes ... Business processes can change in an unpredictable way. ... the cost of that decision necessarily had to include the cost of ...
    (comp.databases.oracle.server)