February 6th, 2004, 01:28 AM
cascadiung updates in 9i
oracle 8 and 8i provide us cascading deletes using foreign key and primary key relationships..means that when we delete the record in primary table, a;; records corresponding to that primary key in the detail table are deleted...is there and cascading updates options in oracle 9i such that if we update a promary key then all corresponding records in the detail table are also updated to new value of primary key???
February 6th, 2004, 09:24 AM
I dont think so you can update the primary key unless you drop it then create new record or disable the primary key first then make changes (but what happend if you have child records? as my understanding oracle will not allow you to update the primary key until you have child record). Oracle provides a new option SET NULL for CASCADE clause, to set the child values null (does not delete it).
February 6th, 2004, 11:59 PM
thanx a lot dear..can u please guide me how to use this clause????
weather in update statement or while applying constraint while creating the table
February 9th, 2004, 10:15 AM
You can use this option only in CREATE TABLE or ALTER TABLE command, as follows:
CREATE TABLE DEPT(
CONSTRAINT dept_deptno_pk PRIMARY KEY
CREATE TABLE EMP (
empno number (2)
deptno number (2)
REFERENCES dept ON DELETE SET NULL
because of ON DELETE SET NULL clause , if deptno 20 is deleted from dept table, then oracle sets to null the value of deptno for all employees in the emp table who previously had deptno 20.