Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to update PK and FK values if foreign key is defined as ON UPADTE NO ACTION?

    Hi,

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


    Sample
    Code:
    TableName: DB2ADMIN.PARENT
    INDEXP
    -----------
              2
    
    TableName: DB2ADMIN.CHILD
    INDEXP      INDEXC
    ----------- -----------
              2           1
              2           2
    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:

    Code:
    CREATE TABLE DB2ADMIN.PARENT (INDEXP INT NOT NULL PRIMARY KEY)
    CREATE TABLE DB2ADMIN.CHILD (INDEXP INT NOT NULL, INDEXC INT NOT NULL, PRIMARY KEY(INDEXP,INDEXC))
    ALTER TABLE DB2ADMIN.CHILD ADD FOREIGN KEY (INDEXP) REFERENCES DB2ADMIN.PARENT (INDEXP) ON DELETE NO ACTION ON UPDATE NO ACTION
    INSERT INTO DB2ADMIN.PARENT VALUES (2)
    INSERT INTO DB2ADMIN.CHILD VALUES (2,1)
    INSERT INTO DB2ADMIN.CHILD VALUES (2,2)

    Thanks,
    Grofaty

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Oct 2005
    Posts
    109
    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
    Juliane

Posting Permissions

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