Re: Updates and primary keys...

Andreas Koch (mail_at_kochandreas.com) wrote:
: Hi,

: what is the usual way to update a table including its primary keys?

You're not updating the primary key in the following. Updating a primary key normally consists of adding a new record which is similar to the original and then removing the original. The two steps would be a single transaction.

: simplified example:

: CustId CustNo CustName
: 1234 1 Abe
: 5678 2 Guest
: 90AB 3 XP
: CDEF 4 Zak

: CustID is primary key, CustNo is unique key.

: CustNo's are given after the alphabetical order of the names,
: so add a Customer "new" , he would get No 3 and XP would get 4.

: If this is processed in a linear order we would do:

: insert into customer values ('FOO',3,'New');
: update customer set CustNo=4 where CustId='90AB';
: update customer set CustNo=5 where CustId='CDEF';

: Obviously, this will fail because the first 2 statement cause
: key violations.

: I can think of 3 methods to handle this:

: a) Sort the processing so that it goes from highest to lowest
: customer no. This would quite a pain with my current data
: modell, at least reguarding "elegance" :)
: b) truncate the table, and re-insert ALL the records, changed or
: not. Not good once you have MANY records, or external links
: (& triggers).

: Any other ideas how to handle this? A "only check keys on
: commit" would be nice :)

The design is wrong.

o What's the point of the CustNo if it can change at any time?

o adding a single customer could require you to modify every single row   in the table. Clearly that is wrong.

I would suggest you rethink something. Received on Thu Apr 25 2002 - 16:44:28 CDT