Re: Inserting a new PK into an existing table

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:

1. The "old" primary key is composed of multiple columns (whose specific combinations remain unique, which will still be enforced by a unique constraint).
2. 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).

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?

> 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 Received on Thu Aug 31 2006 - 11:28:50 CDT