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

From: Vasily Kraskovsky <kvr_at_pe.spb.ru>
Date: 1997/07/24
Message-ID: <ABogmctOB2@pe.spb.ru>#1/1

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; Table altered.

SQL> INSERT INTO t VALUES (1, 'name 1', null);

1 row created.

SQL> INSERT INTO t VALUES (2, 'name 3', 1);

1 row created.

SQL>
SQL> COMMIT; Commit complete.

SQL> CREATE TABLE r
  2 (
  3 id_1 NUMBER,
  4 id_2 NUMBER
  5 )
  6 ;

Table created.

SQL> ALTER TABLE r
  2 ADD CONSTRAINT r#id_1#fk
  3 FOREIGN KEY (id_1) REFERENCES t
  4 ON DELETE CASCADE; Table altered.

SQL>
SQL> ALTER TABLE r
  2 ADD CONSTRAINT r#id_2#fk
  3 FOREIGN KEY (id_2) REFERENCES t
  4 ON DELETE CASCADE; Table altered.

SQL> INSERT INTO r VALUES (1, 2);

1 row created.

SQL>
SQL> COMMIT; Commit complete.

SQL> SELECT COUNT (*)
  2 FROM t
  3 WHERE parent_id NOT IN
  4 (
  5 SELECT id
  6 FROM t
  7 );

COUNT(*)

SQL> SELECT * FROM t;

       ID NAME                             PARENT_ID
--------- -------------------------------- ---------
        1 name 1
        2 name 3                                   1

SQL> DELETE FROM t WHERE id = 1;

1 row deleted.

SQL> SELECT * FROM t;

       ID NAME                             PARENT_ID
--------- -------------------------------- ---------
        2 name 3                                   1

!!! ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
!!! Here is the problem. A row with such parent_id

!!! must be deleted.
!!!

SQL> SELECT COUNT (*)
  2 FROM t
  3 WHERE parent_id NOT IN
  4 (
  5 SELECT id
  6 FROM t
  7 );

COUNT(*)

SQL> spool off

EOF

--
                         Nemo solus sapit satis
         <<<<<<<<<<<<<<<<<<<<<<<<<< >>>>>>>>>>>>>>>>>>>>>>>>>>
Vasily Kraskovsky, Oracle DBA, Petersburg Express, St.Petersburg, Russia
                           vasily_at_kvr.spb.ru
                             kvr_at_pe.spb.ru

Received on Thu Jul 24 1997 - 00:00:00 CDT