Re: select + insert vs. insert and catching exception

"Karsten Farell" <kfarrell_at_medimpact.com> wrote in message news:%HKk9.3204$UN.84147252_at_newssvr21.news.prodigy.com...
> Okay, here's what I was thinking about when I wrote that. Perform this
> little test:
>
> create table t1 (
> pk number not null,
> constraint pk1 primary key (pk)
> );
> create table t2 (
> pk number not null,
> fk number
> );
> alter table t2 add (
> constraint fk2 foreign key (fk) references t1 (pk)
> );
> insert into t1 values (100);
> insert into t2 values (200, 100);
> update t1 set pk=999 where pk=100;
> ERROR at line 1:
> ORA-02292: integrity constraint (owner.FK2) violated - child record found
>

Hi Karsten,

But this is to be expected. Now write a trigger on the parent table that, before update, locates all child records and updates *them*, and then the update you're trying to do the parent will succeed. Cascading updates, in other words.

Regards
HJR
> I admit this is adding a FK parent-child relationship. I should have
> been more specific (or is it more generic) in my statement. I apologize
> for any misunderstanding. Hey, I'm only human.
>
> Howard J. Rogers wrote:
> > "Karsten Farell" <kfarrell_at_medimpact.com> wrote in message
> > news:q5Gk9.3137$lj7.78759108_at_newssvr21.news.prodigy.com...
> >
> >>While conceptually true, you would introduce an application problem if
> >>you make the phone number a PK.
> >>In Oracle, you cannot modify the value of a PK.
> >
> >
> > As written, that statement is off the scale of incorrectness. Of course
you
> > can modify a primary key. I do it all the time... as does any update to
any
> > column executed through an Oracle Forms app, usually.
> >
> > Perhaps you were thinking of foreign keys? Even then the statement is
only
> > half-right: the primary key of the child table in a parent-child
> > relationship is perfectly updateable.
> >
> > Of course, updating the primary key of a parent table is more
problematic,
> > because of the dependent child records. But even there, it's possible to
do
> > a 'cascaded update' by judicious use of triggers (or, rather more
> > drastically, a simple disabling of the foreign key constraint).
> >
> > There is nothing intrinsically special about a primary key that prevents
it
> > being updateable.
> >
> > Regards
> > HJR
>
Received on Fri Sep 27 2002 - 16:46:25 CDT