Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
3 replies Latest Post - ‏2008-02-02T01:13:08Z by Stan
SystemAdmin
SystemAdmin
1525 Posts
ACCEPTED ANSWER

Pinned topic Multiple Foreign Keys from child to same parent invalid if one has delete set null constraint

‏2007-07-25T22:04:18Z |
This works in DB2, why does it fail in Derby? It seems as if the on delete set null constraint with multiple references to a parent should work on derby as well. Is this a bug?

I could not find any restriction concerning SET NULL rule and not being able to have more than one relationship with SET NULL.

From the Derby reference manual

Deleting a row from the parent table involves other tables. Any table involved in a delete operation on the parent table is said to be delete-connected to the parent table. The delete can affect rows of these tables in the following ways:
If the delete rule is RESTRICT or NO ACTION, a dependent table is involved in the operation but is not affected by the operation. (That is, Derby checks the values within the table, but does not delete any values.)
If the delete rule is SET NULL, a dependent table's rows can be updated when a row of the parent table is the object of a delete or propagated delete operation.
If the delete rule is CASCADE, a dependent table's rows can be deleted when a
parent table is the object of a delete.
If the dependent table is also a parent table, the actions described in this list apply, in turn, to its dependents.
I create 2 tables, emp and address. Emp has 2 foreign key references to address; home_adr and work_adr

this fails.
create table address (address_id int primary key not null, street varchar(30))
create table emp ( empid int primary key not null, work_adr int, home_adr int, name varchar(30))
alter table emp add foreign key (work_adr) references address on delete set null
alter table emp add foreign key (home_adr) references address on delete set null
the last alter table fails with
Message:Foreign Key 'SQL070725024204290' is invalid because 'The table cannot be defined as a dependent of table APP.ADDRESS because of delete rule restrictions. (The relationship causes the table to be delete-connected to the indicated table through multiple relationships and the delete rule of the existing relationship is SET NULL.). '.

this fails.
create table address (address_id int primary key not null, street varchar(30))
create table emp ( empid int primary key not null, work_adr int, home_adr int, name varchar(30))
alter table emp add foreign key (home_adr) references address
alter table emp add foreign key (work_adr) references address on delete set null
Message:Foreign Key 'SQL070725025425850' is invalid because 'the delete rule of foreign key must be NO ACTION. (The relationship would cause the table to be delete-connected to the same table through multiple relationships and such relationships must have the same delete rule (NO ACTION, RESTRICT or CASCADE).) '.Vendor code:30000

this works.
create table address (address_id int primary key not null, street varchar(30))
create table emp ( empid int primary key not null, work_adr int, home_adr int, name varchar(30))
alter table emp add foreign key (work_adr) references addressalter table emp add foreign key (home_adr) references address
Updated on 2008-02-02T01:13:08Z at 2008-02-02T01:13:08Z by Stan
  • Stan
    Stan
    267 Posts
    ACCEPTED ANSWER

    Re: Multiple Foreign Keys from child to same parent invalid if one has delete set null constraint

    ‏2007-07-26T01:12:24Z  in response to SystemAdmin
    This seems like a bug to me. Would you report this problem by logging a JIRA report at Apache? The process is described at:

    http://db.apache.org/derby/DerbyBugGuidelines.html

    I did a quick search of JIRA and did not see this problem reported. I checked with the latest release candidate (10.3.1.3) and the problem exists there.
  • SystemAdmin
    SystemAdmin
    1525 Posts
    ACCEPTED ANSWER

    Re: Multiple Foreign Keys from child to same parent invalid if one has de

    ‏2008-02-01T06:48:11Z  in response to SystemAdmin
    Yes, I've got the same problem either, but I am using 10.1 Derby.
    • Stan
      Stan
      267 Posts
      ACCEPTED ANSWER

      Re: Multiple Foreign Keys from child to same parent invalid if one has de

      ‏2008-02-02T01:13:08Z  in response to SystemAdmin
      To get this resolved you will to take the issue to the Derby development community at Apache (derby-dev@db.apache.org). I'm not certain that different delete rules can be allowed on a table but the developers can address that issue. If nothing else the related problem described in Derby-3078 could be addressed to make it easier to resolve these inconsistencies.

      https://issues.apache.org/jira/browse/DERBY-3078