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;

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'."

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.

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?

urquel,
In the example I jost posted, all of the delete rules are set null and
it still rejects with the same error message.

Did you try just creating the USERS and NODES tables without the teams table and then create the two foreign keys?

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?

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.

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.

I don't think that you can call a "bug" something that is clearly stated in the manual.
Well, may be not so clearly :-), but you get the idea.

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.

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.