Re: Inserting a new PK into an existing table

Jens Lenge wrote:
> Mark D Powell 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.
>
> In general, I would agree. In this specific case however, I think it
> could be desirable to have an additional primary key column because:
>
> a) The "old" primary key is composed of multiple columns (whose
> specific combinations remain unique, which will still be enforced by a
> unique constraint).

So the pseudoKey gains you nothing here (in fact it causes more overhead for this table).

>
> b) Unlike before, the table is now going to be referenced by several
> other tables via foreign keys. Without the new artificial primary key,
> this would require multiple new columns in all of these tables (along
> with a foreign key that is composed of these columns).

So? That is what relational data models do.
>
> I thought the new primary key would be a good idea to reduce redundancy
> and simplify the structure, because now I only need to add one new
> column to each of the other tables. Or is it better to use a different
> approach?

The Only benefit fo using the pseudoKey is the space savings, not reduced redundancy.
All you are really doing is replacing a compound PK with a pointer. As long as you are aware of the risks and benefits involved it is okay. Where possible I prefer to maintain the compound Key in both parent and child tables. But the pseudoKey sometimes wins out if for example the number of columns in the compound key is many or the total space is large. In these days of really cheap disc space, most often it's the number of columns that tips the scale in favor of the pseudoKey.

>
> > You can populate a numeric column with unique values by performing an
> > update statement that references the rownum for each row in the table.
> > [...]
>
> Perfect solution. Thank you!
>
> Jens

I also hate rownum, but this is a good example of some usefulness from it.
HTH,
  Ed Received on Thu Aug 31 2006 - 12:33:17 CDT