Re: on update cascade

A copy of this was sent to jeanch_at_my-deja.com (if that email address didn't require changing) On Wed, 23 Feb 2000 13:56:57 GMT, you wrote:

>hi folks,
>
>I was wondering if on can do 'on update cascade'
>to allow foreign key to be changed by oracle
>when the primary key is altered. For instance
>
>tab1(key) key is primary key for tab1
>tab2(key_FK) is foreign keys
>pointing back to tab1
>If I do
>update tab1 set key = 2 where condition...
>then in tab2 key_fk is updated to reflect that
>change.
>
>Any idea how I can achieve this.
>Cheers
>JC
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

See http://osi.oracle.com/~tkyte/update_cascade/index.html for one implementation.

Also, consider deferrable constraints in Oracle8.0 and up. There you can set a foreign key to be deferrable. You could easily write a procedure "update_primary_key" that updates the parent key and all of the children after deferring the constraints. That would be faster then a trigger based solution.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Feb 23 2000 - 10:54:43 CST