Oracle: ON DELETE CASCADE

Sunday, 18.03.2007 – Dejan

Sa mnom u firmi radi nekoliko Java programera, kojima katkad treba objasniti kako funkcionisu neke stvari u Oracle bazi. Nakon sto sam njima objasnio kako funkcionise klauzula “ON DELETE CASCADE“, odlucio sam i ovdje napisati nesto o tome.

Konkretno, pita mene kolega:”Ako ja iz jedne parent tabele obrisem jedan unos, sta ce biti sa unosima u child tabeli? Da li ja moram da vodim evidenciju o tim promjenama i onda u Javi isprogramiram da se obrisu i svi referencijalni unosi u child tabeli?

Moj odgovor je bio kratak i jasan – “ON DELETE CASCADE“.

Pa da objasnim na jednom primjeru.

Imamo parent tabelu pod nazivom “KATEGORIJE_PROIZVODA” i jednu child tabelu pod nazivom “PROIZVODI“. Ukoliko iz tabele “KATEGORIJE_PROIZVODA” obrisemo neku kategoriju, svi referencijalni unosi u tabeli “PROIZVODI” trebaju takodje biti obrisani.

Ovdje mozete skinuti DDL i DML izraze potrebne za ovaj primjer (download DDL Create statements; download DML Insert statements), a ovako izgleda rezultat nakon izvrsavanja tih izraza:

C:>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on So März 18 16:35:58 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn scott/scottpass

Connect durchgef³hrt.

SQL> CREATE TABLE KATEGORIJE_PROIZVODA
  2  (
  3    ID     NUMBER NOT NULL,
  4    NAZIV  VARCHAR2(32 BYTE),
  5    OPIS   VARCHAR2(64 BYTE),
  6    CONSTRAINT pk_kat_proizvod PRIMARY KEY (ID) USING INDEX
  7  );

Tabelle wurde erstellt.

SQL> CREATE SEQUENCE SCOTT.SEQ_KAT_PROIZVOD_ID
  2    START WITH 1
  3    MAXVALUE 99999999999999
  4    MINVALUE 1
  5    NOCYCLE
  6    NOCACHE
  7    ORDER;

Sequence wurde erstellt.

SQL> CREATE OR REPLACE TRIGGER TR_KAT_PROIZVOD_ID
  2  BEFORE INSERT
  3  ON SCOTT.KATEGORIJE_PROIZVODA

  4  REFERENCING NEW AS NEW OLD AS OLD
  5  FOR EACH ROW
  6  BEGIN
  7
  8     SELECT SEQ_KAT_PROIZVOD_ID.NEXTVAL INTO :NEW.ID FROM dual;
  9
 10  EXCEPTION
 11    WHEN OTHERS THEN
 12      NULL;
 13  END tr_kat_proizvod_id;
 14  /

Trigger wurde erstellt.

SQL> CREATE TABLE PROIZVODI
  2  (
  3    ID            NUMBER NOT NULL,
  4    NAZIV         VARCHAR2(32 BYTE),
  5    KAT_PROIZ_ID  NUMBER,
  6    CONSTRAINT pk_proizvod PRIMARY KEY(ID) USING INDEX,
  7    CONSTRAINT fk_kat_proizvod FOREIGN KEY (kat_proiz_id)
  8      REFERENCES KATEGORIJE_PROIZVODA(ID)
  9      ON DELETE CASCADE
 10  );

Tabelle wurde erstellt.

SQL> CREATE SEQUENCE SCOTT.SEQ_PROIZVOD_ID
  2    START WITH 1
  3    MAXVALUE 99999999999999
  4    MINVALUE 1
  5    NOCYCLE
  6    NOCACHE
  7    ORDER;
Sequence wurde erstellt.

SQL> CREATE OR REPLACE TRIGGER TR_PROIZVOD_ID
  2  BEFORE INSERT
  3  ON SCOTT.PROIZVODI
  4  REFERENCING NEW AS NEW OLD AS OLD

  5  FOR EACH ROW
  6  BEGIN
  7
  8     SELECT SEQ_PROIZVOD_ID.NEXTVAL INTO :NEW.ID FROM dual;
  9
 10  EXCEPTION
 11    WHEN OTHERS THEN
 12      NULL;
 13  END tr_proizvod_id;
 14  /

Trigger wurde erstellt.

SQL> DECLARE
  2    lnKat1 NUMBER;
  3    lnKat2 NUMBER;
  4    lnKat3 NUMBER;
  5  BEGIN
  6     INSERT INTO KATEGORIJE_PROIZVODA (naziv, opis)
  7     VALUES('Cokoladice', 'Mljac njam')
  8     RETURNING ID INTO lnKat1;
  9
 10     INSERT INTO KATEGORIJE_PROIZVODA (naziv, opis)
 11     VALUES('Alkoholna pica', 'Bolje biti pijan, nego star')
 12     RETURNING ID INTO lnKat2;
 13
 14     INSERT INTO KATEGORIJE_PROIZVODA (naziv, opis)
 15     VALUES('Cigare', 'Pusenje ubija')
 16     RETURNING ID INTO lnKat3;
 17
 18     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 19     VALUES ('Snickers', lnKat1);
 20
 21     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 22     VALUES ('Snickers Cruncher', lnKat1);
 23
 24     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 25     VALUES ('Duplo', lnKat1);
 26
 27     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 28     VALUES ('Mars', lnKat1);
 29
 30     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 31     VALUES ('Zivotinjsko carstvo', lnKat1);
 32
 33     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 34     VALUES ('Milky Way', lnKat1);
 35
 36     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 37     VALUES ('Twix', lnKat1);
 38
 39     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 40     VALUES ('Heineken pivo', lnKat2);
 41
 42     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 43     VALUES ('Tuborg pivo', lnKat2);
 44
 45     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 46     VALUES ('Bavaria pivo', lnKat2);
 47
 48     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 49     VALUES ('Budweiser pivo', lnKat2);
 50
 51     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 52     VALUES ('Marlboro', lnKat3);
 53
 54     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 55     VALUES ('Morava', lnKat3);
 56
 57     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 58     VALUES ('Ronhill', lnKat3);
 59
 60     INSERT INTO PROIZVODI (naziv, KAT_PROIZ_ID)
 61     VALUES ('Davidoff', lnKat3);
 62  END;
 63  /

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL>

Nakon sto smo kreirali tabele i unijeli podatke, pogledajmo trenutni sadrzaj tih tabela. 

SQL> SELECT * FROM KATEGORIJE_PROIZVODA;
        ID NAZIV                            OPIS
---------- -------------------------------- --------------------------------
         1 Cokoladice                       Mljac njam
         2 Alkoholna pica                   Bolje biti pijan, nego star
         3 Cigare                           Pusenje ubija
SQL> SELECT * FROM proizvodi;
        ID NAZIV                            KAT_PROIZ_ID
---------- -------------------------------- ------------
         1 Snickers                                    1
         2 Snickers Cruncher                           1
         3 Duplo                                       1
         4 Mars                                        1
         5 Zivotinjsko carstvo                         1
         6 Milky Way                                   1
         7 Twix                                        1
         8 Heineken pivo                               2
         9 Tuborg pivo                                 2
        10 Bavaria pivo                                2
        11 Budweiser pivo                              2
        12 Marlboro                                    3
        13 Morava                                      3
        14 Ronhill                                     3
        15 Davidoff                                    3
15 Zeilen ausgewõhlt.

Ukoliko su uneseni podaci ispravni, obrisacemo kategoriju “Cigare“, jer je zakonom zabranjeno pusenje na javnim mjestima i nije vise isplativo prodavati cigare. 😉

Posto imamo referencijalni integritet (Foreign key) izmedju parent i child tabele, u child tabeli “PROIZVODI” bi svi proizvodi iz kategorije “Cigare” trebali biti obrisani.

SQL> DELETE FROM KATEGORIJE_PROIZVODA
  2  WHERE ID = 3;
1 Zeile wurde gelöscht.

Provjerimo jos jednom sadrzaj obe tabele i uvjerimo se da su obrisani i kategorija i svi proizvodi iz te kategorije.

SQL> SELECT * FROM KATEGORIJE_PROIZVODA;
        ID NAZIV                  OPIS
---------- ---------------------- ---------------------------
         1 Cokoladice             Mljac njam
         2 Alkoholna pica         Bolje biti pijan, nego star
SQL> SELECT * FROM proizvodi;
        ID NAZIV                   KAT_PROIZ_ID
---------- ----------------------- ------------
         1 Snickers                           1
         2 Snickers Cruncher                  1
         3 Duplo                              1
         4 Mars                               1
         5 Zivotinjsko carstvo                1
         6 Milky Way                          1
         7 Twix                               1
         8 Heineken pivo                      2
         9 Tuborg pivo                        2
        10 Bavaria pivo                       2
        11 Budweiser pivo                     2
11 Zeilen ausgewõhlt.

Jednostavno i efikasno. Java programeri ne moraju vise da vode brigu o parent-child vezama prilikom brisanja podataka.

Ukoliko ne zelite da podaci iz child tabele budu obrisani, mozete umjesto klauzule “ON DELETE CASCADE” koristiti klauzulu “ON DELETE SET NULL“, pri cemu ce kat_proiz_id dobiti vrijednost NULL ukoliko bude obrisana pripadajuca kategorija iz parent tabele.


  1. 6 Responses to “Oracle: ON DELETE CASCADE”

  2. A da taj Java programer nije isuviše plaćen?
    Mislim, referencijalni integritet je ipak jedna od izrazito bitnih stvari za nekoga ko radi sa bazama podataka…

    By Petar Marić on Mar 19, 2007

  3. Ma zna on sta je Foreign key i tako to, ali je i mene iznenadilo da nije cuo za “ON DELETE CASCADE”.
    Btw. placeni su previse. 🙂 Ni shef nije zadovoljan njima, pa dvojica od 1. aprila vise nece raditi kod nas u firmi.

    Iznenadio bi se koliko ima dobro placenih programera, a da nisu hardcore strucnjaci…

    By dejan on Mar 19, 2007

  4. “Btw. placeni su previse.”
    Hm, koja je firma u pitanju i koliko plaćaju dobre programere? Za oko godinu dana završavam fakultet, pa ako im bude trebalo kadrova 😉

    By Petar Marić on Mar 19, 2007

  5. Jedan dobija 45, a drugi 50 EUR po satu brutto.

    Ako mozes izganjati radnu dozvolu za Austriju, slobodno dodji. 🙂
    Firma je Erste Sparinvest (u vlasnistvu Erste banke): http://www.sparinvest.com

    By dejan on Mar 20, 2007

  6. Pozdrav Dejane,
    evo slucajno nabasah na ovaj Blog (ili vec sta je…) i moram da priznam da sam naso par odlicnih tekstova. Meni kao pocetniku sa Oraclom objasnjenja su sasvim jasna i opsirna. Samo tako, inace su retka mesta na NET-u sa ovom tematikom na nasem jeziku…
    Drugo: jeste da nema veze sa temom al’ sam procitao da je u pitanju Austrija…
    Slucajno i ja svoj hleb zaradjujem u A i upravo se pripremam za OCA ispit u petak. Jel’ mogu da te ugnjavim sa par pitanja na mail?

    By igor M. on May 28, 2007

  7. @igor: Naravno da mozes. 🙂 Salji na adresu dejanAToutsourcing-it.com

    By dejan on May 28, 2007

Post a Comment