Re: select + insert vs. insert and catching exception

From: Karsten Farell <kfarrell_at_medimpact.com>
Date: Thu, 26 Sep 2002 21:09:15 GMT
Message-ID: <%HKk9.3204$UN.84147252@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

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 Thu Sep 26 2002 - 16:09:15 CDT