Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2016
    Posts
    1

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

    I have 2 Tables , please check images attached

    PK: Click image for larger version. 

Name:	PK.PNG 
Views:	2 
Size:	2.9 KB 
ID:	17213

    FK: Click image for larger version. 

Name:	FK.PNG 
Views:	1 
Size:	1.6 KB 
ID:	17214

    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

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Code:
    --#SET TERMINATOR @
    
    create view pk_v as select * from pk@
    
    create or replace trigger pk_v_iur
    instead of update on pk_v
    referencing new as n old as o
    for each row
    begin atomic
      if (n.p_id<>o.p_id) then
        insert into pk (p_id, last_name, ...) values (n.p_id, n.last_name, ...);
        update fk set p_id=n.p_id where p_id=o.p_id;
        delete from pk where p_id=o.p_id;
      end if;
    end@
    
    update (select * from pk_v order by p_id desc) set p_id=p_id+10@
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

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