[gunbilegt]

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;

[Littlefoot]

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.