Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2003
    Posts
    58

    Question Unanswered: Problems adding two foreign keys to the same table

    I have a table NODES which currently has a nullable column NODEOWNER
    with a foreign key to a table called USERS. When I try to add another
    foreign key to the USERS table, I get:

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0632N FOREIGN KEY "C3959625" is not valid because the table cannot be
    defined as a dependent of table "MSTRDATA.USERS" because of delete rule
    restrictions (reason code = "3"). SQLSTATE=42915

    If both delete rules are simply set to SET NULL, how is this a probem?

    If this is a lame DB2 restriction, should I just add the column and not
    set it as a foreign key, or should I add a table NODEBACKUPS that has
    two foriegn keys, NODEID, and BACKUPOWNER?

    Here is the SQL:

    CREATE TABLE MSTRESRC.NODES
    (NODEID INTEGER NOT NULL,
    NODENAME VARCHAR(24) NOT NULL,
    NODEOWNER INTEGER,
    BACKUPOWNER INTEGER);

    ALTER TABLE MSTRESRC.NODES
    ADD CONSTRAINT C2553553 PRIMARY KEY (NODEID);

    ALTER TABLE MSTRESRC.NODES
    ADD CONSTRAINT C0339718 FOREIGN KEY (NODEOWNER)
    REFERENCES MSTRDATA.USERS(ID)
    ON DELETE SET NULL
    ON UPDATE RESTRICT;

    ALTER TABLE MSTRESRC.NODES
    ADD CONSTRAINT C3959625 FOREIGN KEY (BACKUPOWNER)
    REFERENCES MSTRDATA.USERS(ID)
    ON DELETE SET NULL
    ON UPDATE RESTRICT;

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    Are there any other tables/foreign keys in the database that would have any impact on the delete rule? I found this associated to your error in the DB2 manual: "The relationship would cause the table to be delete-connected to the same table through multiple paths and such relationships must have the same 'delete-rule'."

  3. #3
    Join Date
    Aug 2003
    Posts
    58
    Well, NODES also have a TEAMID column which has a RESTRICT DELETE
    rule to the TEAMS table which has a TEAMLEAD column that has a RESTRICT
    DELETE rule to the USERS table, but that should only be preventing
    users that are team leads from being deleted, or teams that own machines
    from being deleted. Both the NODES TEAMID, and the TEAMS TEAMLEAD
    are nullable so I could also make them SET NULL, but I dont think that would
    fix this problem.

    Here is what I just tried:

    create table mstrdata.foou (userid integer not null, name varchar(24) not null)

    alter table mstrdata.foou add constraint XU1 primary key (userid)


    create table mstrdata.foot (teamid smallint not null, name varchar(24) not null, teamlead integer)

    alter table mstrdata.foot add constraint XT1 primary key (teamid)

    alter table mstrdata.foot add constraint YTU1 foreign key (teamlead) references mstrdata.foou(userid) on delete set null on update restrict

    create table mstrdata.foon (name varchar(24) not null, teamid smallint, owner integer, backup integer)

    alter table mstrdata.foon add constraint YNU1 foreign key (owner) references mstrdata.foou(userid) on delete set null on update restrict

    alter table mstrdata.foon add constraint YNU2 foreign key (backup) references mstrdata.foou(userid) on delete set null on update restrict

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0632N FOREIGN KEY "YNU2" is not valid because the table cannot be defined
    as a dependent of table "MSTRDATA.FOOU" because of delete rule restrictions
    (reason code = "3"). SQLSTATE=42915

    The error message you found implies I have a different delete-rule but you can see they are the same. I think the algorithm senses two foreign keys to the same table and then has a stroke rather than figure out if they are the same or not.

  4. #4
    Join Date
    Aug 2004
    Posts
    330
    So, if you attempt to delete a row on the user table, the new foreign keys you are trying to create would provide an alternate cascade "path" from the USERS table to the NODES table. If you have multiple cascade paths with different delete rules, how will the database know how to treat an attempted delete? RESTRICT via the TEAMS->NODES foreign keys, or SET NULL via the NODES foreign keys?
    Last edited by urquel; 10-13-04 at 17:33.

  5. #5
    Join Date
    Aug 2003
    Posts
    58
    urquel,
    In the example I jost posted, all of the delete rules are set null and
    it still rejects with the same error message.

  6. #6
    Join Date
    Aug 2004
    Posts
    330
    Did you try just creating the USERS and NODES tables without the teams table and then create the two foreign keys?

  7. #7
    Join Date
    Aug 2003
    Posts
    58
    db2 create table mstrdata.foou (userid integer not null, name varchar(24) not null)

    db2 alter table mstrdata.foou add constraint XU1 primary key (userid)

    db2 create table mstrdata.foon (name varchar(24) not null, teamid smallint, owner integer, backup integer)

    db2 alter table mstrdata.foon add constraint YNU1 foreign key (owner) references mstrdata.foou(userid) on delete set null on update restrict

    db2 alter table mstrdata.foon add constraint YNU2 foreign key (backup) references mstrdata.foou(userid) on delete set null on update restrict

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0632N FOREIGN KEY "YNU2" is not valid because the table cannot be defined
    as a dependent of table "MSTRDATA.FOOU" because of delete rule restrictions
    (reason code = "3"). SQLSTATE=42915


    Can someone else try this on their version?

  8. #8
    Join Date
    Aug 2004
    Posts
    330
    One other thing. Try removing the "update restrict". I had a lot of trouble with this error (on db2 mainframe) and the error message never points at the what the problem is. I had to hack at it for a while to find it.

  9. #9
    Join Date
    Aug 2003
    Posts
    58
    Removing the update restrict had no effect, it still failed. Sounds like I have hit something that not to many people use or this bug would have been fixed.

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I don't think that you can call a "bug" something that is clearly stated in the manual.
    If T1 is delete-connected to T2 through multiple paths, those relationships in which T1 is a dependent and which form all or part of those paths must have the same delete rule and it must not be SET NULL. The NO ACTION and RESTRICT actions are treated identically. Thus, if T1 is a dependent of T3 in a relationship with a delete rule of r, the referential constraint cannot be defined when r is SET NULL if any of these conditions exist:
    - T2 and T3 are the same table
    - T2 is a descendant of T3 and the deletion of rows from T3 cascades to T2
    - T3 is a descendant of T2 and the deletion of rows from T2 cascades to T3
    - T2 and T3 are both descendants of the same table and the deletion of rows from that table cascades to both T2 and T3.
    Well, may be not so clearly :-), but you get the idea.

  11. #11
    Join Date
    Aug 2003
    Posts
    58
    Actually I dont get the idea. What does that say in English?
    What I dont get is how asking the database to set a value to null if the
    row I am keying on is deleted is a big deal.

    I want a Node to have up to two users associated with it. If either
    of those users gets deleted, remove the association, not the Node.

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by wayneb64
    Actually I dont get the idea. What does that say in English?
    Actually, English is not my native language but to me the quoted passage means that you can't have more than one referential integrity constraint that has the SET NULL option between two tables . As to why this is the case, I'm afraid only IBM can tell.

Posting Permissions

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