Constraining tables and ORA-04094

From: MarkyG <markg_at_mymail.tm>
Date: 18 Sep 2001 02:28:47 -0700
Message-ID: <ab87195e.0109180128.9894f7f@posting.google.com>

Hi,

Sorry, reposted a previous message but this time with an example.

I'm trying to delete a record from one parent table and need to remove the child records down the 'tree' structure.

I have 2 identical databases table wise and with the same indexes and integrity constraints. The only difference is that one DB is version 8.1.7 (8i) and the other 8.0.6.

The delete works fine on the 8i database but on the 8.0.6 database, i get an ORA-04094: table DGNAU.TGROUPITN is constraining... error.

Question is:
Is there an init.ora parameter which makes the deletes work in one version but not the other or is it a new feature of 8i which makes the delete work.

Below is a sample script to experiment with.

Opinions welcome ;-)

Mark

---

drop table temp1 cascade constraints;
create table temp1

(id	number,
name	varchar2(10))

/

drop table temp2 cascade constraints;
create table temp2

(id	number,
 id2	number,
address	varchar2(10))

/

drop table temp3 cascade constraints;
create table temp3

( id2	number,
  age	varchar2(10));

/

alter table temp1
add constraint pk_id primary key (id);

alter table temp2
add constraint pk2_id primary key (id2);

alter table temp2
add constraint fk_id foreign key (id)
references temp1(id);

alter table temp3
add constraint fk2_id foreign key (id2)
references temp2(id2);

insert into temp1 values(1, 'Fred');
insert into temp2 values(1, 20, 'Hounslow');
insert into temp3 values(20, '57');

CREATE OR REPLACE TRIGGER trg1
  BEFORE DELETE ON TEMP1 FOR EACH ROW
BEGIN
  DELETE FROM TEMP2
  WHERE ID = :OLD.ID;
END;
/

CREATE OR REPLACE TRIGGER trg2
  BEFORE DELETE ON TEMP2 FOR EACH ROW
BEGIN
  DELETE FROM TEMP3
  WHERE ID2 = :OLD.ID2;
END;
/

delete from temp1;
/
Received on Tue Sep 18 2001 - 04:28:47 CDT