Constrangeri

10.CONSTRANGERI

Obiective

Dupa parcurgerea acestui capitol trebuie sa fiti capabili sa:

definiti o constrangere
creati o constrangere

Ce sunt constrangerile?

Constrangeri

Servarul Oracle utilizeaza constrangeri pentru a preveni patrunderea de date invalide in tabele

Putem utiliza constrangeri pentru a realiza umatoarele:

Impune reguli datelor unui tabel ori de cate ori un rand este inserat, updatat sau sters din tabel.
Previne stergerea unui tabel in cazul in care exista dependenta de alte tabele
Furnizeaza reguli pentru utilitatile Oracle, cum ar fi ORCALE DEVELOPER

Tipuri de constrangeri

Constrangere	Descriere
NOT NUL	specifica faptul ca o coloana nu poate avea valoarea nula
UNIQUE	specifica o coloana sau o combinatie de coloane a carei valori trevuie sa fie unice pentru toate randurile din tabel
PRIMARY KEY	identifica fiecare rand al tabelei
FOREIGN KEY	stabileste o relatie de cheie straina intre coloana si coloana tabelei de referinta
CHEcK	specifica o conditie care trebuie sa fie adevarata

Ghidul Constrangerilor

Toate constrangerile sunt cuprinse intr-un dictionar. Este usor sa se faca referinta la constrangeri daca li se da nume sugestive. Numele unei constrangeri trebuie sa urmeze un anumit standard. Daca nu se denumeste constrangerea, servarul Oracle genereaza un nume de forma SYS_Cn, unde n este un numar intreg astfel incat numele constragerii este unic.

Constrangerile definite pentru un anumit tabel pot fi vizualizate in USER_CONSTRAINTS (dictionarul tabelei).

Definirea Constrangerilor

Sintaxa

CREATE TABLE [schema.]table
(column datatype [DEFAULT expr]
[column_constraint],
[table_constraint][...]);

In sintaxa mai sus prezentata:

schema	este aceeasi ca si numele titularului
table	este numele tabelei
DEFAULT expr	specifica o valoare predefinita care sa fie utilizata daca o valoare este omisa indeclaratia INSERT
column	este numele coloanei
datatype	este tipul datei si lungimea coloanei
column_constraint	este o constata de integritate ca parte a definitiei coloanei
table_constraint	este o constanta de integritate ca parte a definitiei tabelei

EXEMPLU

Adaugarea unei constrangeri unui tabel odata cu creearea lui

De obicei constrangerile sunt create in acelasi timp cu tabelul.Constrangerile pot fi adaugate tabelului dupa creearea lui.

Constrangerile pot fi definite pe 2 nivele:

Nivelul constrangerii	Descriere
Coloana	Face referire la o singura coloana; poate defini orice tip de constrangere de integritate
Tabel	Face referire la una sau ma multe coloane; poate defini orice constrangere exceptand pe cea de tip NOT NUL

Sintaxa

1. Constrangere la nivel de coloana

column [CONSTRAINT constraint_name] constraint_type

2. Constrangere la nivel de tabel

column,..
[CONSTRAINT constraint_name] constraint_type
(column,...),

In sintaxa avem:

constraint_name	este numele constrangerii
constraint_type	este tipul constrangerii

Constrangerea NOT NULL

Constrangerea de tip NOT NULL asigura faptul ca o coloana sa nu contina valoarea nula. Ea poate fi specificata la nivel de coloana si nu la nivel de tabel.

Exemplu

In exemplul urmator constrangerea NOT NULL se aplica coloanelor ENAME si HIREDATE din tabelul newemp. Pentru coloana ENAME constragerea nu este denumita astfel incat servarul Oracle o sa creeze un nume pentru ea.Pentru coloana HIREDATE constrangerea este denumita: "...CONSTRAINT hiredate_nn NOT NULL...".

Acum vom incerca sa inseram valori doar in coloanele EMPNO, SAL, COMM, dar la executareane va da eroare pentru ca ENAME si HIREDATE au valori nule iar constrangerile ne obliga sa le atribuim o valoare.

Constrangerea UNIQUE

Constrangerea UNIQUE de integritate impune ca fiecare valoarea a unei coloane sau set de coloane sa fie unica-doua randuri ale aceluias tabel sa nu aiba aceleasi valori intr-o anumita coloana sau set de coloane. Permite includerea de valori nule numai daca constrangerea NOT NULL nu este definita pentru aceeasi coloana(valoarea nula nu este considerata a fi echivalenta cu ceva). Constrangerea UNIQUE poate fi definita atat la nivel de linie cat si la nivel de tabela.

Exemplu

In exemplul de mai jos se aplica constrangerea UNIQUE coloanei EMAIL a tabelei newemp1.Numele constrangerii este empmail_un.

Constrangerea Cheie Primara

Constrangerea CHEIE PRIMARA creeaza o cheie primara pentru tabel. Numai o singura cheie poate fi creata pt fiecare tabel. Aceasta constrangere este o coloana sau un set de coloane care identifica in mod unic fiecare rand al tabelului.Nici o coloana care face parte din cheia primara nu poate contine valoarea nula. Poate fi definita la nivel de coloana sau tabel.

Un tabel poate avea o singura cheie primara dar poate avea mai multe constrangeri de tip UNIQUE.

Exemplu

In exemplul de mai jos este definita o constrangere de tip cheie primara la nivelul coloanei DEPTNO a tabelei DEPARTAMENTE. Numele contrangerii este deptno_pk

Constrangerea FOREIGN KEY

Constrangerea FOREIGN KEY defineste o coloana sau o combinatie de coloane ca foreign key si stabileste o relatie intre o cheie primara si una unica in acelas tabel sau in tabele diferite. O valoare care apare intr-un tabel trebuie sa se regaseasca si in cel de-al 2-lea tabel, pe coloana unde formeaza cheia primara. Constrangerile de tip FOREIGN KEY pot fi definite la nivel de coloana sau tabel.

Exemplu

In urmatorul exemplu se defineste o constrangere de tip FOREIGN KEY coloanei DEPTNO a tabelei NEWEMP2 utilizand sintaxa la nivel de tabel.

Se poate defini si la nivel de coloana.Sintaxa este urmatoarea:

Dupa cum am observat a disparut din sintaxa FOREIGN KEY.

O contrangere de tip foreign key este definita intr-un tabel copil, si tabelul care contine coloana la care se face referinta este parintele. O foreign key este definita utilizand o combinatie a urmatoarelor cuvinte cheie:

FOREIGN KEY este utilizata pentru a defini o coloana in tabelul copil la nivel de tabel
REFERENCES identifica tabela si coloana in tabelul parinte
ON DELETE CASCADE indica faptul ca atuci cand randul din tabelul parinte va fi sters , randul dependent din tabelul copil va fi de asemenea sters.
ON DELETE SET NULL converteste valorile foreign key in valori nule atunci cand valoarea parinte este stearsa.

Constrangerea de tip CHECK

Constrangerea de tip CHECK defineste o conditie pe care fiecare rand trebuie sa o satisfaca.Urmatoarele expresii nu sunt permise:

Referinte la pseudocoloanele CURRVAL, NEXTVAL, LEVEL si ROWNUM
Apelul functiilor SYSDATE, UID, USER, si USERENV
cereri care se refera la alte valori ale altor randuri

Nu exista un nr limitat de constrangeri de tip CHECK pe care sa le definim pe o coloana.

Constragerea de tip check poate fi definita atat la nivel de coloana cat si la nivel de tabel.

Exemplu

Adaugarea unei constrangeri

Se poate aduga o constrangere pentru un tabel existent utilizand ALTER TABLE si clauza ADD.

Sintaxa

ALTER TABLE table
ADD [CONSTRAINT constraint] type(column);

In sintaxa avem:

table	este numele tebelului
constraint	este numele constrangerii
type	este tipul constrangerii
column	este numele coloanei afectate de constrangere

Observatii

Se poate adauga, sterge, activa sau dezaciva o constrangere, dar nu-i putem modifica structura.
Se poate dauga o constrangere NOT NULL la o coloana existenta utilizand clauza MODIFY a declaratiei ALTER TABLE.

Exemplu

SE poate adauga o constrangere si unui tabel existent(nu numai odata cu creerea lui).In urmatorul exemplu vom creea o constrangere FOREIGN KEY in tabela EMP. Constrangerea asigura existenta unui manager daca exista angajat in tabela EMP.

Stregerea unei constrangeri

Pentru a sterge o constrangere trebuie utilizata declaratia ALTER TABLE cu clauza DROP. Optiunea CASCADE a clauzei DROP face ca si constrangerea dependenta sa fie stearsa.

Sintaxa

ALTER TABLE table
DROP PRIMARY KEY| UNIQUE (column)|
CONSTRAINT constraint [CASCADE];

In sintaxa avem

table	este numele tebelului
constraint	este numele constrangerii
column	este numele coloanei afectate de constrangere

Exemplu

1.In exemplul ilustrat vom sterge constrangerea la nvelul coloanei MGR din tabel.

2. In urmatoul exemplu vom sterge constrangerea cheie primara din tabelul DEPARTAMENTE si cheia strina asociata coloanei DEPTNO din tabela NEWEMP2.

Dezactivarea unei constrangeri

Dezactivarea constrangerii se efectueazacu declaratia ALTER TABLE insotita de clauza DISABLE.

Sintaxa

ALTER TABLE table
DISABLE CONSTRAINT constraint [CASCADE];

In sintaxa avem

table	este numele tebelului
constraint	este numele constrangerii

Observatii

Se poate utiliza clauza DISABLE atat in declaratia CREAT TABLE cat si inALTER TABLE
Clauza CASCADE dezactiveaza constrangeri de integritate dependente

Exemplu

Activarea unei constrangeri

Se poate activa o constrangere fara a o sterge sau recreea utilisand AlTER TABLE cu clauza ENABLE.

Sintaxa

ALTER TABLE table
ENABLE CONSTRAINT constraint [CASCADE];

In sintaxa avem

table	este numele tebelului
constraint	este numele constrangerii

Observatii

Daca se activeaza o constrangere, acesta constrangere se aplica la toate datele din tabel.
Daca se activeaza o constrangere UNIQUE sau PRIMARY se creeaza automat un index UNIQUE sau PRIMARY.
Clauza ENABLE se poate utiliza in ambele declaratii CREAT TABLE cat si ALTER TABLE.

Exemplu

Constrangeri Cascadate

Constrangerile de tip CASCADE sunt utilizate cu clauza DROP COLUMN.
Constrangerea CASCADE sterge toate constrangerile de integritate ce se refera la cheile primare si unice definite in coloanele sterse.
Sterge de asemenea toate constrangerile multicoloane definite in coloanele sterse.

Vizualizare Constrangeri

Dupa ce creem un tabel putem verifica existenta lui utilizand o comanada DESCRIBE.Singura constrangere ce se poate verifica este constrangerea NOT NULL.

Pentru a se vizualiz toate constrangerile din tabel trebuie interogat tabelul USER-CONSTRAINTS

Exemplu

Exercitii Propuse

1.Adaugati o constrangere PRIMARY KEY tabelei EMP pe coloana EMPNO. Constrangerea trebuie de nuita la creeare. NUmele constrangerii este my_emp_id_pk.

2.Stergeti constrangerea creata mai sus.

3.Vizualizati constrangerile din tabela EMP.

Pentru a vizualliza raspunsurile le intrebarile de mai sus, apasati aici: RASPUNSURI

Capitolul precedent

Acasa

Capitolul urmator