Oracle: ON DELETE CASCADE
Sunday, 18.03.2007 – DejanSa 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.
6 Responses to “Oracle: ON DELETE CASCADE”
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
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
“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
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
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
@igor: Naravno da mozes. 🙂 Salji na adresu dejanAToutsourcing-it.com
By dejan on May 28, 2007