Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    25

    Unanswered: Problem Updating.

    CREATE TABLE NOASSETTAG
    (
    TaglessNo Integer NOT NULL,
    Inv_Location Varchar(10) NOT NULL,
    PRIMARY KEY (TaglessNO),
    FOREIGN KEY (Inv_Location) REFERENCES LOCATION(Place) ON DELETE RESTRICT ,
    );

    CREATE TABLE LOCATION
    (
    Place Varchar(10) NOT NULL,
    Room Varchar(25) NOT NULL,
    PRIMARY KEY(Place)
    );

    I have these 2 tables in DB2 7.0. Whenever i triy to update the place column, i keep getting an error The Parent Key in a relationship cannot be updated. Is there anyway of updating it? Someone mention using a trigger. Is a trigger possible of solving my problem?

  2. #2
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64

    Re: Problem Updating.

    Yes

    You can NOT update the parent key with this structure. You must instead create a new LOCATION row and then copy any and all NOASETTAG rows having relationship whit that LOCATION. Triggers cannot solve this fundamental problem.

    Cheers, Bill

  3. #3
    Join Date
    Jan 2004
    Posts
    25

    Re: Problem Updating.

    Originally posted by hurmavi
    Yes

    You can NOT update the parent key with this structure. You must instead create a new LOCATION row and then copy any and all NOASETTAG rows having relationship whit that LOCATION. Triggers cannot solve this fundamental problem.

    Cheers, Bill
    Thanks for the reply and your help but i am new to DB2 and though i understand what you are talking about creating a new row, i do not know how to implement it. Can you give me some headway?

  4. #4
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64

    Re: Problem Updating.

    OK!

    Run following SQL, of course after editing New & Old name

    INSERT INTO LOCATION
    SELECT 'New Name'
    , Room
    FROM LOCATION
    WHERE Place = 'Old Name';

    -- this will create new copy of Place-row
    -- Then copy all NOASSETTAG rows

    INSERT INTO NOASSETTAG
    SELECT TaglessNo
    , 'New Name'
    FROM NOASSETTAG
    WHERE Inv_Location = 'Old Name' ;

    -- Then delete all old Rows with 'Old Name'

    DELETE FROM NOASSETTAG
    WHERE Inv_Location = 'Old Name' ;

    DELETE FROM LOCATION
    WHERE Place = 'Old Name' ;


    Cheers, Bill

  5. #5
    Join Date
    Jan 2004
    Posts
    25
    Thanks for the fast reply. will take a look and post the outcome.

Posting Permissions

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