Re: ON DELETE CASCADE PROBLEM

From: <asprusch_at_my-dejanews.com>
Date: Mon, 27 Jul 1998 06:20:58 GMT
Message-ID: <6ph68a$cjf$1@nnrp1.dejanews.com>

We have 7.3.3 too, and it works properly. The following script without more dependencies to other! tables maybe can validate the correect behaviour of the on delete cascade option. If this don't work, there's maybe a corruption in the database. Otherwise there are triggers which will cause the problem.
drop table parent cascade constraints;
create table parent (pk integer);
alter table parent add constraint pk_parent primary key (pk);

drop table child_1 cascade constraints; create table child_1 (pk integer, ck integer); alter table child_1 add constraint pk_child_1 primary key (pk); alter table child_1 add constraint fk_child_1 foreign key (ck) references parent (pk) on delete cascade;

drop table child_2 cascade constraints; create table child_2 (pk integer, ck integer); alter table child_2 add constraint pk_child_2 primary key (pk); alter table child_2 add constraint fk_child_2 foreign key (ck) references parent (pk) on delete cascade;

insert into parent values (1);
insert into parent values (2);
insert into parent values (3);
insert into parent values (4);
insert into parent values (5);
insert into parent values (6);
insert into parent values (7);

insert into child_1 values (1,1);
insert into child_1 values (2,1);
insert into child_1 values (3,1);
insert into child_1 values (4,3);
insert into child_1 values (5,3);
insert into child_1 values (6,3);
insert into child_1 values (7,4);
insert into child_1 values (8,4);
insert into child_1 values (9,4);
insert into child_1 values (10,4);
insert into child_1 values (11,7);
insert into child_1 values (12,7);
insert into child_1 values (13,7);
insert into child_1 values (14,7);

insert into child_1 values (15,7);
insert into child_1 values (16,7);

insert into child_2 values (1,2);
insert into child_2 values (2,2);
insert into child_2 values (3,2);
insert into child_2 values (4,3);
insert into child_2 values (5,3);
insert into child_2 values (6,3);
insert into child_2 values (7,5);
insert into child_2 values (8,5);
insert into child_2 values (9,5);
insert into child_2 values (10,5);
insert into child_2 values (11,6);
insert into child_2 values (12,6);
insert into child_2 values (13,6);
insert into child_2 values (14,6);
insert into child_2 values (15,7);
insert into child_2 values (16,7);

commit;

Hope this will help
Andreas Prusch

In article <6pdamc$hrb$1_at_supernews.com>,   "Zafer Mutlu AKTAN" <zafer_at_redshift.com> wrote:
> Hi,
> I have 2 child tables with columns let's say TB1C1, TB1C2 (in the first
> table) and TB2C1, TB2C2 (second table) which are referencing to the same
> parent table's (let's say TBPARENT) primary key column with a foreing key
> constraint which ends with the "ON DELETE CASCADE" option. In other words
> if I delete a record from the parent table TBPARENT and if there are records
> referencing this one in TB1C1 , TB1C2, TB2C1 and/or TB2C2 , those will
> automatically be purged to keep the referential integrity intact. The
> problem is here: IT DOES NOT WORK PROPERLY (in 7.3.3). It deletes the
> records from TB1 but not from TB2. If I change the order of the child table
> creation then this reverses (TB2 works fine but not TB1 ?) . I wonder if
> there is a non-documented constraint for ON DELETE CONSTRAINT related with
> the number of it's use in a child table for the same parent table field.
> (The parent table contains the force_side_names and the child tables use
> this record as force_sideA_force_sideB relationship definitions).
> Any help is greatly appreciated.
>
> Zaf
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Mon Jul 27 1998 - 01:20:58 CDT