CODEWITHSUNDEEP
databasedb2db2-400
Swat
Swat

Reputation: 13

How can we update an column with Foreign key constraint in DB2?

I have 2 Tables , please check images attached

PK: PK

FK: FK

P_Id in Pk table is the primary key and P_Id in FK table is the foreign key.

I need to add 10 to all records in P_Id column of both PK and FK table( meaning they need to match always)

I know in MS SQL we can easily update cascade as follows:

ALTER TABLE FK
ADD CONSTRAINT FK_P_Id
FOREIGN KEY (P_Id)
REFERENCES PK (P_Id) ON UPDATE CASCADE

and then update the rows of PK , which will automatically update FK too.

update A
set A.P_Id= A.P_Id + 10
from PK A inner join FK B
on A.P_Id = B.P_Id

But, i am not sure how this works in DB2.. can someone please help?

How can i get this to work?

Thanks in advance Swat

Upvotes: 1

Views: 22919

Answers (3)

Pratik Kashyap
Pratik Kashyap

Reputation: 41

use 

SET FOREIGN_KEY_CHECKS = 0;

update both the tables;

and then

SET FOREIGN_KEY_CHECKS = 1;

Upvotes: 5

Esperento57
Esperento57

Reputation: 17462

--remove you foreign key
ALTER TABLE YOURLIB.FK 
drop CONSTRAINT YOURLIB.FK_P_Id;

--update FK table
update  YOURLIB.FK 
set P_Id=P_Id+10;

--update PK table (force)
update  YOURLIB.PK overriding system value 
set P_Id=P_Id+10;

--recreate foreign key
ALTER TABLE YOURLIB.FK 
ADD CONSTRAINT YOURLIB.FK_P_Id 
FOREIGN KEY (P_Id)
REFERENCES YOURLIB.PK (P_Id)
ON DELETE RESTRICT;

--If you id on PK is autoincremented, restart it (here 123456 in example but you must found max of id  in your PK table --> select max(p_id) from yourlib.pk)
ALTER TABLE YOURLIB.PK
ALTER COLUMN P_Id
RESTART with 123456;

Upvotes: 3

Esperento57
Esperento57

Reputation: 17462

you can modify a key to force like this, only if you update dont create double value key (example your key + 10 already exist in your table), else you must remove the primary key before update (Dangerous, be carefull if someone are working on your table). Ok course you must remove foreign key for do it

update pk f0 overriding system value
set f0.id=f0.id+10
where exists 
(
    select * from fk f1
    where f0.id=f1.id
)

Upvotes: 0

Related Questions