Updates and primary keys...

Hi,

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

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:

1. 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" :)
2. 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 :)

(DB: 8.1.7)

-- 
                                                   Andreas
if ((sex==fem)&&(hobby==comp)&&(age~25)&&(status==single)
&&(cntry=GER)) goto http://www.kochandreas.com/home/single.htm