Intretinerea integritatii datelor Oracle9I

Pentru pastrarea integritatii datelor exista trei modalitati principale:

prin codul aplicatiei (proceduri la nivelul bazei de date sau aplicatii ce ruleaza pe statiile client);

prin triggeri (declansatori) - sunt programe PL/SQL care se executa automat cand are loc un eveniment (cum ar fi inserarea sau actualizarea unei coloane), programe ce sunt create pentru a putea implementa reguli complexe de integritate a datelor;

prin constrangeri de integritate - reprezinta cel mai utilizat mecanism de implementare a regulilor de integritate.

Tipuri de constrangeri

Serverul Oracle recunoaste urmatoarele tipuri de constrangeri:

NOT NULL - specifica faptul ca o coloana nu poate contine valori nule;

Exemplu:

SQL> ALTER TABLE produse

MODIFY (tip_prod NOT NULL);

UNIQUE - desemneaza o coloana sau o combinatie de coloane ca avand valori, respectiv combinatii de valori unice;

Exemplu:

SQL> ALTER TABLE produse

ADD CONSTRAINT nume_prod_unq UNIQUE (nume_prod);

PRIMARY KEY - desemneaza o coloana sau o combinatie de coloane ca fiind cheia primara a tabelei (se activeaza automat si constrangerea de NOT NULL pe coloana sau coloanele respective);

Exemplu:

SQL> ALTER TABLE produse

ADD CONSTRAINT prk_prod_id PRIMARY KEY (prod_id);

FOREIGN KEY - desemneaza o coloana sau o combinatie de coloane ca fiind cheie externa intr-o restrictie de integritate referentiala (tabela pe care este definita cheia externa este fiul unei alte tabele, numita tabela-parinte si care are pe coloanele referite o cheie primara sau unica);

Exemplu:

SQL> ALTER TABLE furnizor

ADD CONSTRAINT frk_prod_id FOREIGN KEY (prod_id)

REFERENCES produse (prod_id)

CHECK - specifica o conditie pe care fiecare rand al tabelei trebuie sa o satisfaca.

Exemplu:

SQL> ALTER TABLE produse

ADD CONSTRAINT chk_pret CHECK (pret_unitar > 0);

Starea constrangerilor

Constrangerile pot fi in una din urmatoarele stari:

DISABLE NOVALIDATE - datele din tabel, precum si cele nou introduse pot sa nu respecte conditiile impuse de constrangere;

DISABLE VALIDATE - se interzice orice modificare a coloanei supuse restrictiei (nu se pot adauga, edita sau sterge date);

ENABLE NOVALIDATE - datele ce vor fi introduse trebuie sa respecte conditiile impuse de constrangere, in timp ce datele deja existente in tabel pana la momentul trecerii in aceasta stare pot incalca regula constrangerii;

ENABLE VALIDATE - toate datele (atat cele existente cat si cele ce urmeaza a fi introduse) trebuie sa respecte conditiile impuse de constrangere.

Verificarea constrangerilor

Serverul Oracle poate amana sau nu verificarea constrangerilor pana la sfarsitul tranzactiei. Acest lucru poate fi posibil prin definirea constrangerilor ca fiind imediate sau intarziate dupa cum urmeaza:

constrangeri neintarziate sau imediate (NONDEFERRED sau IMMEDIATE) - forteaza verificarea constrangerii dupa fiecare operatie DML. O violare a constrangerii va avea ca efect revenirea la starea de dinaintea operatiei DML;

constrangeri intarziate (DEFERRED) - sunt constrangerile care sunt verificate doar cand este comisa toata tranzactia. Daca in timpul comiterii tranzactiei apar violari ale constrangerii atunci se revine la starea de dinaintea tranzactiei.

Definirea constrangerilor imediate sau intarziate

Definirea modului de verificare a constrangerilor se poate face in doua moduri:

SET CONSTRAINT | CONSTRAINTS

Aceasta comanda determina tipul constrangerii sau constrangerilor pentru o anumita tranzactie si are efect pe intreaga durata a tranzactiei sau pana la o alta comanda SET CONSTRAINT. Aceasta comanda nu este permisa in corpul declansatorilor (triggers).

ALTER SESSION

SET CONSTRAINT[S] =

Comanda afecteaza toate constrangerile (nu se permite o lista a numelor constrangerilor) si are efect doar pentru sesiunea curenta.

Impunerea constrangerilor de cheie primara si cheie unica

Impunerea acestor tipuri de constrangeri se face folosind indecsi a caror locatie si tip poate fi controlat de utilizator. Serverul Oracle parcurge urmatorii pasi pentru a implementa constrangerile de cheie primara sau unica (fig. 13.5.1):

daca constrangerea este dezactivata nu este nevoie de indecsi;

daca constrangirea este activa, iar coloana(ele) din constrangere formeaza prima parte a unui index (prima(ele) coloana(e) indexate), atunci acest index este folosit pentru impunerea constrangerii indiferent daca el este unic sau nu;

daca constrangirea este activa si nu exista nici un index pe coloana(ele) acesteia atunci se creaza automat un index cu acelasi nume ca si constrangerea dupa urmatoarele reguli:

daca cheia este intarziata atunci se creaza un index non-unic;

daca cheia este imediata atunci se creaza un index unic;

daca un index este disponibil si constrangerea este imediata atunci se poate folosi acel index iar daca constrangerea este intarziata pentru a putea fi folosit indexul trebuie sa fie non-unic.

fig. 13.5.1

Consideratii privind cheile externe

Pentru intretinerea tabelelor ce au chei externe trebuie tinut cont de urmatorii factori:

cheia externa trebuie stearsa inainte de a se sterge tabelul parinte. Ambele actiuni pot fi executate cu ajutorul comenzii:

DROP TABLE nume_tabela CASCADE CONSTRAINTS;

tabelul parinte nu poate fi trunhciat fara a se sterge sau dezactiva cheia externa;

cheia externa trebuie stearsa inainte ca spatiul-tabel, ce contine tabelul parinte, sa fie sters. Acest lucru se poate realiza prin comanda:

DROP TABLESPACE nume_spatiu INCLUDING CONTENTS

CASCADE CONSTRAINTS;

daca optiunea DELETE CASCADE nu este folosita in momentul stergerii de randuri din tabela parinte, serverul Oracle trebuie sa se asigure ca nu exista randuri in tabela fiu care sa corespunda celor sterse. Similar, o actualizare (update) a cheii primare este permisa numai daca nu exista randuri in tabela fiu cu valorile vechi. Daca sunt actualizate concomitent ambele tabele pirn tranzactii diferite va trebui creat un index pe coloana(ele) pe care avem cheia externa.

cand sunt actualizate sau inserate date in tabela fiu, serverul Oracle verifica indexul de pe tabela parinte ce impune cheia referentiala (indexul definit pe coloana(ele) cheii primare). Din acest motiv operatia operatia reuseste doar daca spatiul-tabel ce contine indexul este disponibil (online). De remarcat este faptul ca spatiul-tabel ce contine tabelul parinte nu trebuie neaparat sa fie disponibil.

Definirea constrangerilor in momentul creerii tabelului

Constrangerile pot fi definite fie odata cu crearea tabelului (CREATE TABLE), fie cand acesta este modificat (ALTER TABLE). Acest lucru se realizeaza in ambele comenzi prin folosirea clauzei constrangeri_coloana.

Sintaxa este urmatoarea:

tipdata_coloana [ CONSTRAINT constrangere ]

stare_constrangere :==

[ NOT DEFERRABLE | DEFERRABLE [ INITIALLY

[ DISABLE | ENABLE [ VALIDATE | NOVALIDATE ] ]

unde:

CONSTRAINT - identifica numele constrangerii prin constrangere pastrat in dictionarul de date;

USING INDEX - specifica daca parametrii din clauza_index vor fi folositi pentru impunerea constrangerilor de cheie primara sau unica;

NOT DEFERRABLE sau DEFERRABLE - indica daca constrangerea poate fi intarziata sau nu (implicit este NOT DEFERRABLE);

INITIALLY IMMEDIATE - specifica faptul ca la inceputul fiecarei tranzactii se verifica implicit constrangerea (daca nu se specifica o clauza INITIALLY atunci Oracle va considera constrangerea ca fiind implicit INITIALLY IMMEDIATE);

INITIALLY DEFERRED - implica faptul ca aceasta constrangere poate fi intarziata si ea va fi verificata la sfarsitul fiecarei tranzactii;

DISABLE - dezactiveaza constrangerea de integritate, prin urmare ea nu va fi impusa de serverul Oracle.

Exemplu:

SQL> CREATE TABLE hr.angajati (

id NUMBER(3) CONSTRAINT angajati_id_pk PRIMARY KEY

DEFERRABLE

USING INDEX

STORAGE(INITIAL 100K NEXT 100K)

TABLESPACE index,

Nume VARCHAR2(20) NOT NULL,

dep_id NUMBER(3))

TABLESPACE utilizatori;

Dupa crearea tabelului acesta poate fi modificat prin adaugarea de constrangeri ca in urmatorul exemplu:

SQL> ALTER TABLE hr.angajati

ADD (CONSTRAINT angajati_dep_id_fk FOREIGN KEY(dep_id)

REFERENCES hr.departament(id)

DEFERRABLE INITIALLY DEFERRED);

Activarea constrangerilor

O constrangere ce este momentan dezactivata poate fi activata in unul din urmatoarele doua moduri :

ENABLE NOVALIDATE (nu blocheaza tabelul iar cheile primare si unice trebuie sa foloseasca indecsi non-unici)

Se face prin comanda:

ALTER TABLE [schema.]nume_tabel

ENABLE NOVALIDATE

[ USING INDEX clauza_index]

Pentru cheile primare sau unice activarea in acest mod a unei constrangeri este mult mai rapida deoarece datele existente in tabel nu sunt verificate daca constrangerea este intarziata. Clauza USING INDEX este aplicabila doar cheilor primare sau unice care au fost create ca intarziate si daca una din conditii este adevarata:

constrangerea a fost creata dezactivata;

constrangerea a fost dezactivata ulterior iar indexul sters.

ENABLE VALIDATE (blocheaza tabelul, poate folosi atat indecsi unici cat si non-unici, necesita date valide in tabela)

Activand o constrangere VALIDATE se verifica toate datele din tabela daca nu incalca constrangerea. Acest lucru este implicit atunci cand o constrangere este activata. Activarea validata a unei constrangeri se face cu comanda:

ALTER TABLE [ schema.]nume_tabel

ENABLE [ VALIDATE ]

[ USING INDEX clauza_index]

[ EXCEPTIONS INTO [ schema.]nume_tabel ]

Optiunea VALIDATE poate lipsi (efectul ramane acelasi).

Daca datele din tabela violeaza constrangerea atunci se revine iar constrangerea ramane dezactivata.

Utilizarea tabelului EXCEPTIONS

Clauza EXCEPTIONS identifica orice rand care violeaza o constrangere activata. Consideram urmatoarea tabela: ANGAJATI ce are coloanele ID, NUME, DEP_ID. Pentru detectarea erorilor, rectificarea lor si reactivarea constrangerii se urmeaza urmatoarea procedura:

Daca tabela EXCEPTIONS nu este creata, ea se poate creea prin rularea scriptului utlexcpt1.sql. Exemplu:

SQL> @rdbms/admin/utlexcpt1

Statement processed

SQL> DESCRIBE exceptions

Name  Null? Type

------------- ----- ----- ----------------

ROW_ID  ROWID

OWNER VARCHAR2(30)

TABLE_NAME  VARCHAR2(30)

CONSTRAINT  VARCHAR2(30)

Se executa comanda ALTER TABLE cu clauza EXCEPTIONS. Exemplu:

SQL> ALTER TABLE hr.angajati

ENABLE VALIDATE CONSTRAINT angajati_dep_id_fk

EXCEPTIONS INTO system.exceptions;

ALTER TABLE hr.angajati

ORA-02298: cannot enable (HR.ANGAJATI_DEP_ID_FK) - parent keys not found

In urma acestei comenzi tabela EXCEPTIONS este populata cu randurile care nu satisfac constrangerea. Daca se doreste re-rularea unei astfel de comenzi va trebui golita tabela EXCEPTIONS de toate randurile existente.

Se identifica datele invalide utilizand o sub-interogare a tabelei EXCEPTIONS. Exemplu:

SQL> SELECT rowid, id, nume, dep_id FROM hr.angajati

WHERE rowid in (SELECT row_id FROM exceptions)

FOR UPDATE;

ROWID ID NUME DEP_ID

----- ----- ----------------- --------- ----- ----- --------------- ------------

AAAAeyAAD 194 Popescu Ion 2502

1 rows selected.

Eroarea apare datorita existentei in coloana DEP_ID a valorii 2502, valoare care nu se regaseste in tabela parinte.

Se corecteaza eroarea sau erorile aparute din datele tabelei.Exemplu:

SQL> UPDATE hr.angajati

SET DEP_ID=250 WHERE rowid='AAAeyAAD';

1 row processed.

SQL> COMMIT;

Statement processed.

In urma acestei comenzi s-a actualizat valoarea eronata din coloana ID.

Se goleste tabela EXCEPTIONS si se reactiveaza constrangerea. Exemplu:

SQL> TRUNCATE TABLE exceptions;

Statement processed.

SQL> ALTER TABLE hr.angajati

ENABLE VALIDATE CONSTRAINT angajati_dep_id_fk

EXCEPTIONS INTO system.exceptions;

Statement processed.

In acest moment constrangerea de cheie externa a fost activata si validata.

Obtinerea informatiilor referitoare la constrangerilor

Pentru a obtine diverse informatii despre constrangerile definite la nivelul bazei de date, serverul Oracle pune la dispozitie doua vederi. Prin interogarea acestor vederi (DBA_CONSTRAINTS si DBA_CONS_COLUMNS) se pot obtine toate caracteristicile diferitelor constrangeri din baza de date. Mai jos este prezentat un tabel cu coloanele din DBA_CONSTRAINTS a caror semnificatie nu este evidenta:

Exemplu:

SQL> SELECT c.constraint_name, c.constraint_type, cc.column_name

FROM dba_constraints c, dba_cons_columns cc

WHERE c.owner='HR'

AND c.table_name='ANGAJATI'

AND c.owner=cc.owner

AND c.constrinat_name=cc.constraint_name

ORDER BY cc.position;

CONSTRAINT_NAME C COLUMN_NAME

-------- ----- ------ ----- -- ----- ----- --------- ----- -----

ANGAJATI_DEP_ID_FK  R DEP_ID

ANGAJATI_ID_PK  P ID

SYS_C00565  C NUME

3 rows selected.

Acest exemplu afiseaza toate constrangerile precum si coloanele pe care sunt definite din cadrul tabelei ANGAJATI. Dupa cum se observa ultima constrangere este una generata automat.