triggersdb2

Update the parent table DB2 change primary key and error on foreign key


Request to write and explain the operation of a trigger that will first update the parent table TAB1 (update in the code) and then TAB2. I get this error:

ERROR: SQL ERROR [23504]: THE PARENT KEY IN A PARENT ROW OF RELATIONSHIP "TAB2.TAB2_FK" CANNOT BE UPDATED.. SQLCODE=-531, SQLSTATE=23504, DRIVER=4.25.13

Code:

CREATE TABLE TAB1
(
    ID INTEGER PRIMARY KEY NOT NULL,
    SOMETHING VARCHAR(100) NULL
);

CREATE TABLE TAB2
(
    ID INTEGER PRIMARY KEY NOT NULL,
    TAB1_ID INTEGER NOT NULL
);

ALTER TABLE TAB2 
    ADD CONSTRAINT TAB2_FK 
        FOREIGN KEY (TAB1_ID) REFERENCES TAB1(ID);

INSERT INTO TAB1 (ID, SOMETHING) VALUES (1, 'XYZ');
INSERT INTO TAB1 (ID, SOMETHING) VALUES (2, 'ABC');

INSERT INTO TAB2 (ID, TAB1_ID) VALUES (1, 1);

UPDATE TAB1 
SET ID = 5 
WHERE ID = 1;

Solution

  • DB2 doesn't support UPDATE CASCADE in referential constraints as some other vendors.
    You may try a workaround with a view and INSTEAD OF UPDATE trigger on it. You use namely this view in such UPDATE PK statements, not the base table.
    Notice, that it's not intended for cases when a new value may be in a list of old values like UPDATE TAB1_V SET ID = ID + 1

    --#SET TERMINATOR @
    
    CREATE VIEW TAB1_V AS SELECT * FROM TAB1
    @
    
    CREATE OR REPLACE TRIGGER TAB1_V_IU
    INSTEAD OF UPDATE
    ON TAB1_V
    REFERENCING NEW AS n OLD AS o
    FOR EACH ROW
    BEGIN ATOMIC
      IF (n.ID <> o.ID) THEN 
        INSERT INTO TAB1(ID, SOMETHING) VALUES (n.ID, n.SOMETHING);
        UPDATE TAB2 SET TAB1_ID = n.ID WHERE TAB1_ID = o.ID;
        DELETE FROM TAB1 WHERE ID = o.ID;
      ELSE
        UPDATE TAB1 SET SOMETHING = n.SOMETHING WHERE ID = n.ID;
      END IF;
    END
    @
    
    UPDATE TAB1_V
    SET ID = 5 
    WHERE ID = 1
    @