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

.

Relevant Pages

  • Re: Theoy When Updating Linked Records
    ... and a timestamp as the primary key? ... RI is lost because the pseudoKey is not the same on the Client ... as it is on the Server. ...
    (comp.databases)
  • Re: Using seek to find if a record exists
    ... a compound primary key cannot have Nulls in any of the fields, ... >>so your candidate compound key would be an extremely poor choice ... > poor decision if the birthdate were required for all records. ... where the compound key you describe could possibly have been used. ...
    (comp.databases.ms-access)
  • Re: audit trigger
    ... I am getting a primary key error. ... I initialize @PKCols? ... > Your procedure will not work when you get a compound key. ...
    (microsoft.public.sqlserver.programming)
  • Re: Modelling DB with separate records for each year
    ... WHY DO YOU THINK YOU NEED A PSEUDOKEY FOR A TIME PERIOD?? ... been scanning several pages about Normalization from a google search. ... So it does just seem to be a common practice. ... But overuse of ID columns especially as the Primary key seems ...
    (comp.databases)
  • Re: Trend towards artificial keys (GUIDs) sez my textbook...is AI next?
    ... Sometimes they conflict and one has to make tradeoffs. ... Sometimes no such ideal key exists. ... primary key that includes a PRIMARY key of the first table. ... It is fairly common for something like an order line item to have a compound key. ...
    (comp.databases.theory)