Hi,

I would like to update parent and child table to the same value, but there is foreign key preventing update process.


Sample
On table DB2ADMIN.PARENT is PRIMARY KEY column INDEXP. On table DB2ADMIN.CHILD is PRIMARY KEY column INDEXP and INDEXC. On DB2ADMIN.CHILD is FOREING KEY with definiton ON DELETE NO ACTION ON UPDATE NO ACTION.

Now I am trying to UPDATE both tables column INDEXP to the new value INDEXP=1. Inside Logical Unit of Work I am executing the following SQLs:
UPDATE DB2ADMIN.PARENT SET INDEXP =1 WHERE INDEXP =2
and
UPDATE DB2ADMIN.CHILD SET INDEXP =1 WHERE INDEXP =2

But at first update statement I got the following error:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0531N The parent key in a parent row of relationship
"DB2ADMIN.CHILD.SQL051116110237960" cannot be updated. SQLSTATE=23504


The secound update statement gives the following error:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0530N The insert or update value of the FOREIGN KEY
"DB2ADMIN.CHILD.SQL051116110237960" is not equal to any value of the parent
key of the parent table. SQLSTATE=23503


QUESTION: How to update parent and child table to the new values?



Commands to create Sample:


Thanks,
Grofaty

You are going to have to do it as a 3 step process.

1) INSERT new row into parent table that is the same as the old row except for PK.
2) UPDATE child
3) DELETE old row from parent

Andy

How about:

disable your check constraints and enable later on (SET INTEGRITY .. OFF / IMMEDIATE CHECKED)

or:
insert into parent
update child
delete from parent
commit

both are valid