Re: Updates and primary keys...

"Andreas Koch" <mail_at_kochandreas.com> wrote in message news:aa9dqi$iu9$01$1_at_news.t-online.com...
> 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:
>
> 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 :)
>
> (DB: 8.1.7)
>
> --
> Andreas
> if ((sex==fem)&&(hobby==comp)&&(age~25)&&(status==single)
> &&(cntry=GER)) goto http://www.kochandreas.com/home/single.htm
>

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

This is called deferred constraint checking in Oracle, and has been around since 8.0
The constraint clause in the sql reference manual should provide sufficient additional info.
What happened to the person who came up with the idea for this unique key (hopefully it was not you)? IMO, assigning a key like this is going to be a big PITA.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address