Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Temporarely disabeling constraints

Re: Temporarely disabeling constraints

From: J L Joslin <jjoslin_at_ibm.net>
Date: 1997/10/15
Message-ID: <3445440d.139745772@news-s01.ca.us.ibm.net>#1/1

You'll have to use the following procedure:

ALTER TABLE refered DISABLE CONSTRAINT foreign_key_name; UPDATE main SET key=201 WHERE key=4;
UPDATE refered SET key=201 WHERE key=4; ALTER TABLE refered ENABLE CONSTRAINT foreign_key_name;

Oracle allows updates to primary keys as long as the update does not cause a duplicate key conflict. Simply disable the foreign key constraint, update the primary key value and related column in the child table then enable the constraint once again. The entire child table will be revalidated using the parent's primary key index (which could take a while). You might be better off to copy the child rows to another table, delete them from the child, delete the affected record from the parent, insert a new record in the parent and copy the children from the temporary table after updating them. What you are looking to do is really known as a cascade update. You could accomplish all of these steps with a fairly simple trigger based on an update of the key column in the parent table.

Regards,

Jim Joslin - Principal Consultant (jjoslin_at_us.oracle.com) Oracle Consulting Services - Florida Technology Practice

The opinions expressed herein are my own and do not necessarily represent those of Oracle Corporation. Received on Wed Oct 15 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US