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: Mark D Powell <Mark.Powell_at_eds.com>
Date: 1 Sep 2006 08:33:30 -0700
Message-ID: <1157124810.824060.220760@h48g2000cwc.googlegroups.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

Dave, in my opinion the Hibernate people are full of crap. The problem with an artificial key is that while it might be handy to use in the case where the PK value is subject to change the FK to the generated key does next to nothing to protect the data from business rule violations. In your example where there is a UK on the business column you are probably going to end up with child tables that have both a FK to the PK and a FK to the UK. Why would you need both? When you use generated keys you often end up with extra indexes becuase you have to index the generated PK plus you have to index the business column that the user actually has a value for. Generated keys have plenty of drawbacks if not applied carefully. The best designs will probably include both.

As far as the legacy comment goes: rubish. Generated keys were in part pushed so that you could use native integers as the key as this would be more efficient. But until version 10 Oracle would not even store numeric values as native machine types but rather used the number type for float, integer, number(10) which requires library math.

Design is unfortunately perhaps the most important single aspect of whether an application will perform well. I say unfortunate because the rules on how you normalize your data and organize your data stores into a functional system are not well defined from the following point of view. A team can take a very structured, rigid approach with one application and it can work very well. Using the exact same approach with another problem however can result in a system that does not perform well. The problem in my opinion being the key word:rigid. Some flexibility in how the system is designed based on the data at hand, the data relationships, and how the data is manipulated needs to be built into the system. You will have trouble getting that if you follow a rigid rule like every table must have a unique numeric generated key.

IMHO -- Mark D Powell -- Received on Fri Sep 01 2006 - 10:33:30 CDT

Original text of this message

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