on update cascade 2

[sweetleaf]

if i have table1 with a PK on emp_id and a child table (table2) with an FK referencing the above PK, how do i set on update cascade so that when table1.emp_id is modified the modification also happens on all related records in table2?

thanks

 

[SantaMufasa]

Sweetleaf,

First, Oracle has not implemented "...ON UPDATE CASCADE" syntax, and they probably never will since such syntax enables an abhorrent no-no in the relational world: Among the "7 rules for Well-Behaved Primary Keys" is the rule which states that "Primary Keys should never (need to) change."

Oracle has implemented "...ON DELETE CASCADE" syntax. The specification appears on child tables' FOREIGN KEY/REFERENCES constraint. An example appears here, along with the Primary-Key table definition:

SQL> create table table1 (emp_id number primary key);

Table created.

SQL> create table table2 (id number primary key, emp_id references table1 (emp_id) on delete cascade);

Table created.

SQL>

Dave
Sandy, Utah, USA @ 19:47 GMT, 12:47 Mountain Time

 

[Turkbear]

Hi,
If you want tio try an 'ON UPDATE CASCADE' try the info at AskTom's site:

http://asktom.oracle.com/~tkyte/update_cascade/index.html

hth,

 

[sweetleaf]

thanks so much!

i opted to attach a trigger to the parent table which will propogate changes made to the pk, over to the child table.

cheers!