#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Lahore
    Posts
    16
    Rep Power
    0

    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???
    regards
  2. #2
  3. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    11
    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).
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Lahore
    Posts
    16
    Rep Power
    0
    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
    regards
  6. #4
  7. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    11
    You can use this option only in CREATE TABLE or ALTER TABLE command, as follows:

    CREATE TABLE DEPT(
    deptno number(2)
    CONSTRAINT dept_deptno_pk PRIMARY KEY
    )

    CREATE TABLE EMP (
    empno number (2)
    PRAMRY KEY,
    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.

    Regards

IMN logo majestic logo threadwatch logo seochat tools logo