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

Question

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;

Is this some educational task named like "Fun with crazy trigger processing in DB2" or some real case with very bad architecture requiring update of table PK? You may try create a view on TAB1 and INSTEAD OF UPDATE trigger on it to run the last UPDATE on this view, not on the base table TAB1. Otherwise it's not a single BEFORE UPDATE trigger on TAB1 with INSERT INTO TAB2 of rows with a new TAB1_ID (and generation of new PK values somehow for them), but with an additional AFTER UPDATE trigger on TAB1 with DELETE FROM TAB2 of rows with old TAB1_ID... — Mark Barinstein
– Mark Barinstein, Commented Mar 21 at 11:34
@MarkBarinstein The DB2 database is fun and limited, in PostgreSQL, we have UPDATE CASCADE, and it works great. DB2 is weak and lacks functionalities like many others. — TomW
– TomW, Commented Mar 21 at 12:02
In this case, there is an ID field of type INTEGER, but it can also be VARCHAR, for example, with value = 'APP123' and we want chcange to 'APP222'. — TomW
– TomW, Commented Mar 21 at 12:05
I believe that DB2 intentionally doesn't support UPDATE CASCADE telling you: "don't design database schemas in this way, requiring PK value update in a parent table". It's a very bad practice. — Mark Barinstein
– Mark Barinstein, Commented Mar 21 at 12:12
@MarkBarinstein You are partly right, but I don't have any inpact on strucure right now. This is the structure I was given, and I have to work with it because the application operates on this. I can't change the structure. — TomW
– TomW, Commented Mar 21 at 12:19

Mark Barinstein · Accepted Answer · 2025-03-21 12:18:24Z

0

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
@

answered Mar 21 at 12:18

Mark Barinstein

12.8k2 gold badges11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TomW Mar 21 at 13:13

Thank you for your help. I understand that such solutions are not desirable, but unfortunately, in some cases i need using this.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related