Re: Cascade Update?

Ren?Guitar ¼¶¼g©ó¤å³¹ <3586E7EF.3E51DFF0_at_videotron.ca>...
>Since i've got a new job and get introduce to Oracle, my boss now want
>me to "think" of a little problem for updating records in a new database
>we will implement.
>The problem is when you have a parent table with childs and you want to
>update a primary key record on a cascade base, Oracle will not let you
>modify child table because you have "mutate" the parent table.
>Parent_Table
>(Name varchar2(20), -Primary key
> ...other records...)
>Child_Table
>(Name varchar2(20), -Link to Name in the Parent_table
>...other records...)
>In this little example, i cannot go into the Parent_Table, do an Update
>on the Name record and then go down and change the same record in the
>Child_Table.
>So, before going deeper into this problem i want to know if anybody has
>face this situation and if you can provide me some useful information
>(Web Site, book, package, ideas, etc.) that can help me solve this
>situation.

First, find the constraint name of the foreign key, say Child_Table_Foreign_Key,

    SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS

        WHERE CONSTRAINT_TYPE='R'
            AND TABLE_NAME=Child_Table;

Second, disable the foreign key constraint:

    ALTER TABLE Child_Table DISABLE CONSTRAINT Child_Table_Foreign_Key;

then update your data of Parent_Table and Child_Table, commit the work. After all, re-enable the constraint:

    ALTER TABLE Child_Table ENABLE CONSTRAINT Child_Table_Foreign_Key; Received on Sun Jun 21 1998 - 04:10:06 CDT