Re: Deletion and Cascade. The tree dies.

From: Valery Yourinsky <vsu_at_softexpress.ru>
Date: Thu, 24 Jun 1999 19:48:00 +0300
Message-ID: <37726140@mail.softservice.ru>

Joseph Kagume Ngari <ngari_at_unbc.ca> wrote in message news:37717975.D2FC6E58_at_unbc.ca...
>
>Hi and thanks for reading
>
>The table below is meant to represent a tree. HOwever, when i try and
>delete a record, it deletes all the records. I think it has to do with
>the flagrant use of ON DELETE CASCADE.
>Can you see any way for me to correct this problem.
>If a sibling is deleted, nothing else should be deleted.
>if a parent is deleted, all its children should be deleted.

I think it will be helpful (Oracle8 only!): (EMP is standard Oracle demo table created by script %Oracle_home%\DBS\demobld.sql)

SQL> DROP TABLE emp_1
2 /

Table dropped.

SQL>
SQL> CREATE TABLE emp_1
  2 AS
  3 SELECT * FROM emp
  4 /

Table created.

SQL>
SQL> ALTER TABLE emp_1

  2     ADD CONSTRAINT EMP_1_PRIMARY_KEY
  3     PRIMARY KEY (EMPNO)

4 /

Table altered.

SQL>
SQL> ALTER TABLE emp_1
2 ADD CONSTRAINT EMP_1_SELF_KEY FOREIGN KEY (MGR)

  3      REFERENCES EMP_1 (EMPNO)
  4      ON DELETE CASCADE
  5      DEFERRABLE INITIALLY DEFERRED

6 /

Table altered.

SQL>
SQL> SELECT LPAD(' ',(LEVEL-1)*3)||LEVEL||'. '||ENAME   2 FROM EMP_1
  3 CONNECT BY PRIOR EMPNO=MGR
  4 START WITH MGR IS NULL
  5 /

LPAD('',(LEVEL-1)*3)||LEVEL||'.'||ENAME
----------------------------------------------------------------------------
-------------------

1. KING
2. BLAKE

      3. MARTIN
      3. ALLEN
      3. TURNER
      3. JAMES
      3. WARD
   2. CLARK
      3. MILLER
   2. JONES
      3. FORD
         4. SMITH
      3. SCOTT
         4. ADAMS

14 rows selected.

SQL>
SQL> DELETE FROM emp_1
2 WHERE ENAME = 'BLAKE'
3 /

1 row deleted.

SQL> COMMIT
2 /

Commit complete.

SQL>
SQL> SELECT LPAD(' ',(LEVEL-1)*3)||LEVEL||'. '||ename   2 FROM emp_1
  3 CONNECT BY PRIOR empno=mgr
  4 START WITH mgr IS NULL
  5 /

LPAD('',(LEVEL-1)*3)||LEVEL||'.'||ENAME
----------------------------------------------------------------------------
-------------------

1. KING
   2. CLARK
      3. MILLER
   2. JONES

      3. FORD
         4. SMITH
      3. SCOTT
         4. ADAMS

8 rows selected.

Valery Yourinsky
---
Softservice, Moscow
ORACLE PARTNER
tel/fax (095) 333-63-10, 128-18-21
http://www.softexpress.ru
ICQ# 3689794
second E-mail: vsur_at_usa.net Received on Thu Jun 24 1999 - 11:48:00 CDT