Re: [H] Part II. ON DELETE CASCADE breaks for FK. Complete example. Bug?

From: Craig M. Wall <cwall_at_petersons.com>
Date: 1997/07/24
Message-ID: <MPG.e418530fdb0b629989681@nntp.noc.netcom.net>#1/1

Vasily,

This behavior is a bug which is fixed in 7.3.4. It affects all ports, contact Worldwide support to see if there is a patch for your platform.

Bug Desription:
Data inconsistency can occur when there are several foreign key constraints defined against the same primary key. If these foreign keys are defined with the on delete cascade option a row deleted from the parent may not delete all rows from all the tables with the foreign key definitions. therefore, rows will exist in the foreign key table that have no parent.

The following query can be used by a DBA to see the tables which are at risk.

select b.owner pri_owner, b.table_name pri_table, a.owner for_owner, a.table_name for table from dba_constraints a, dba_constraints b where a.r_constraint_name = b.constraint_name

and a.constraint_type = 'R'
and a.delete_rule = 'CASCADE'
and b.constraint_type = 'P'
and a.r_owner=b.owner
and b.owner not in ('SYS','SYSTEM')
and b.table_name in 

(select table_name from dba_constraints where constraint_type = 'P') group by b.table_name, b.owner, a.table_name, a.owner order by pri_owner;

Craig Wall

In article <ABogmctOB2_at_pe.spb.ru>, kvr_at_pe.spb.ru says...
>
> PROBLEM: After parent record (id = 1) had been deleted
> child record was still remained in table in spite of
> existing FK constraint with ON DELETE CASCADE option.
> REMAINDED RECORD VIOLATES EXISTING CONSTRAINT!
>
> NOTE: No doubt that the point is the second constraint on
> table r, or combination of both of them, cause it all
> works fine till only one constraint exists on table r.
> But why? After recreating of the constraint it starts
> to work but till next database shutdown/startup sequence.
>
> Thanks in advance.
>
> Below you will find a complete example of breaking
> FOREIGN KEY t#parent_id#fk on table t.
>
> =========== =======
> | t | | r |
> |===========| |=======|
> -------->| id | |->> | id_1 |
> | | | name | |->> | id_2 |
> | |--->>| parent_id | | |=======|
> | |===========| |
> | |
> |---------------------------|
>
> -----------------------------------------------------------------------------
> BOF
> -----------------------------------------------------------------------------
> SQL> CREATE TABLE t
> 2 (
> 3 id NUMBER,
> 4 name VARCHAR2(32),
> 5 parent_id NUMBER
> 6 )
> 7 ;
>
> Table created.
>
> SQL> ALTER TABLE t
> 2 ADD CONSTRAINT t#pk
> 3 PRIMARY KEY (id);
>
> Table altered.
>
> SQL> ALTER TABLE t
> 2 ADD CONSTRAINT t#parent_id#fk
> 3 FOREIGN KEY (parent_id) REFERENCES t
> 4 ON DELETE CASCADE;
Received on Thu Jul 24 1997 - 00:00:00 CDT