Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2008
    Posts
    40

    Unanswered: Updating Parent Key Problem

    EMPLOYEES(ENO,ENAME,ZIP,HDATE)
    PARTS(PNO,PNAME,QOH,PRICE,LEVEL)
    CUSTOMERS(CNO,CNAME,STREET,ZIP,PHONE)
    ODETAILS(ONO,PNO,QTY)
    ZIPCODES(ZIP,CITY)


    I tried to to update zipcodes.zip but got following error

    ORA-02292: integrity constraint (SYSTEM.SYS_C003997) violated - child record
    found
    ORA-06512: at "SYSTEM.UPDATEZIP", line 6
    ORA-06512: at line 1



    Following is the block i'm using:


    CREATE OR REPLACE PROCEDURE UPDATEZIP(OLDZIP IN NUMBER,
    NEWZIP IN NUMBER)
    IS
    BEGIN

    UPDATE ZIPCODES
    SET ZIP = NEWZIP
    WHERE ZIP = OLDZIP;

    END UPDATEZIP;

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    ORA-06512: at "SYSTEM.UPDATEZIP", line 6
    Bad, BAD idea to use SYSTEM as a schema for your own pleasure. If you continue to do that, I bet you'll regret it some day.

    As of a problem you have, it is quite obvious: you can't modify 'zipcodes.zip' to a new value as long as there are child records which rely on "old" values. If it was allowed, there would be a mess once you decide to write a qurey which should return records based on join with the 'zipcodes' table.

    Based on what you've written, 'zip' column appears in 'employees' and 'customers' tables. So, how to change 'zipcodes' table?

    One way would be to create additional table which would contain pairs of old and new zip codes. Then INSERT new zip codes into the 'zipcodes' table, update 'employees' and 'customers' to point to newly added zip codes, and then (if you wish) remove "old" zip codes from the 'zipcodes' table.

    Or, you might modify foreign key constraints to be deferrable; that way you could UPDATE 'zipcodes' table, then update 'employees' and 'customers' and - once it is done - COMMIT the changes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •