Transcript definirea_constrangerilor
Slide 1
Definirea constrângerilor
Slide 2
Obiective
În această lecție veți învăța :
• Să definiți și descrieți constrângerile
• Să definiți constrângerile la nivel de tabelă
• Să definiți constrângerile la nivel de
coloană
Slide 3
Dobândirea cunoștințelor
Așa cum am precizat în partea de
proiectare a bazelor de date, orice bază de
date trebuie să stabilească regulile de
integritate care să garanteze că datele
introduse sunt corecte și valide. Asta
înseamnă că dacă există o regulă sau
restricție asupra unei entități, atunci datele
introduse în baza de date respectă aceste
restricții.
Slide 4
Dobândirea cunoștințelor
• Regulile de integritate se definesc la
crearea tabelelor folosind constrângerile.
• În funcţie de nivelul la care sunt definite
avem:
- constrângeri la nivel de tabelă care pot
acţiona asupra unei combinaţii de coloane
- constrângeri la nivel de coloană
Slide 5
Dobândirea cunoștințelor
• Constrângerile pot fi clasificate în:
- constrângeri de domeniu, care definesc
valorile pe care le poate lua un atribut ( NOT
NULL, UNIQUE, CHECK)
- constrângeri de integritate a tabelei, precizând
cheia primară a acesteia
- constrângeri de integritate referenţială, care
asigură coerenţa între cheile primare şi
cheile străine corespunzătoare
Slide 6
Dobândirea cunoștințelor
• Orice constrângere într-o bază de date are un nume.
Numele îl putem da noi la crearea constrângerii , ca
de exemplu clienti_id_client_pk , sau nu și în acest
caz sistemul denumește constrângerea, ca de
exemplu SYS_C00585417.
• Un mod de a denumi o constrângere este o
combinație între numele tabelei, numele coloanei la
care se referă și tipul constrângerii(uk, nn, ck, pk, fk):
nume-tabelă_nume-coloană_tipul-contrângerii
• Este recomandat să denumim noi constrângerile
deoarece numele dat de sistem este greu de regăsit.
Slide 7
Dobândirea cunoștințelor
Constrângerea NOT NULL
Exemplu:
• Necompletarea în tabelă a unei
celule conduce la completarea ei cu
valoarea NULL, semnificând faptul că
celula respectivă are de fapt o
valoare nedefinită.
• Coloanele dintr-o tabelă
corespunzătoare atributelor obligatorii
din ERD trebuie obligatoriu
completate, ele nu pot conţine
valoarea NULL.
• Pentru definirea acestei
restricţii se foloseşte restricţia NOT
NULL.
• Constrângerile NOT NULL se pot
defini doar la nivel de coloană.
• Restricţia NOT NULL poate fi folosită
împreună cu clauza DEFAULT.
• În exemplul de mai jos valoarea
câmpului nume nu poate fi NULL,
deoarece se va genera o eroare.
CREATE TABLE agenti_inchirieri
(id_ang NUMBER(5),
nume VARCHAR2(20) NOT NULL,
data_ang DATE DEFAULT SYSDATE,
salariu NUMBER(5),
id_adresa NUMBER(5),
id_sef NUMBER(5),
data_nast DATE,
permis VARCHAR2(5),
comision NUMBER(5))
Slide 8
Dobândirea cunoștințelor
Constrângerea UNIQUE
Exemplu:
CREATE TABLE altii
• Aceasta desemnează o coloană sau o
combinaţie de coloane ca o cheie unică.
(id_ang NUMBER(5),
• Două linii în aceeaşi tabelă nu pot avea
nume VARCHAR2(20),
aceeaşi valoare pentru această cheie.
• Coloanele definite ca UNIQUE pot
data_ang DATE,
conţine valori NULL, iar acestea pot fi
oricâte, adică valoarea NULL este singura salariu NUMBER(5),
valoare ce poate fi duplicată într-o coloană id_adresa NUMBER(5) UNIQUE,
UNIQUE, alte valori nu pot fi duplicate.
• Sintaxa constrângerii la nivel de tabelă: id_sef NUMBER(5),
[CONSTRAINT nume constrangere]
functia VARCHAR2(20) ,
UNIQUE (Coloana, Coloana, ...)
CONSTRAINT altii_uk UNIQUE
• Sintaxa constrângerii la nivel de coloană:
(nume, data_ang))
[CONSTRAINT nume constrangere]
UNIQUE
Slide 9
Dobândirea cunoștințelor
Constrângere de cheie primară
• Ca şi la cheile unice, o cheie primară
forţează unicitatea unei coloane sau
combinaţii de coloane implicate şi un
index unic este creat pentru a conduce
aceasta.
• NULL-urile nu sunt permise în coloanele
de chei primare.
• Sintaxa constrângerii la nivel de tabelă :
[CONSTRAINT nume constrangere]
PRIMARY KEY (Coloana, Coloana, ...)
• Sintaxa constrângerii la nivel de coloană :
[CONSTRAINT nume constrangere]
PRIMARY KEY
• Aceeaşi combinaţie de coloane nu poate
fi folosită şi pentru o cheie primară şi
pentru una unică.
Exemplu:
CREATE TABLE inchirieri
( id_locuinta NUMBER(5),
id_client NUMBER(5),
data_preluarii DATE,
data_predarii DATE,
CONSTRAINT inchirieri_pk PRIMARY
KEY(id_locuinta, id_client))
În exemplul de mai sus s-a definit
o constrângere de cheie
primară la nivel de tabelă, iar
cheia primară este formată din
două câmpuri id_locuinta și
id_client.
Slide 10
Dobândirea cunoștințelor
Constrângere de cheie străină
• Cheile străine furnizează reguli de integritate
de referinţă înăuntrul unei tabele sau între
tabele.
• O cheie străină este folosită oriunde într-o
relaţie cu fiecare cheie primară sau unică.
• Sintaxa constrângerii la nivel de tabelă:
[CONSTRAINT nume constrangere]
FOREIGN KEY (Coloana, Coloana, ...)
REFERENCES tabela (Coloana, Coloana,...)
• Sintaxa constrângerii la nivel de coloană:
[CONSTRAINT nume constrangere]
REFERENCES tabela (Coloana)
• Cuvintele 'FOREIGN KEY' nu sunt folosite în
cazul constrângerii la nivel de coloană a
sintaxei.
Exemplu:
CREATE TABLE clienti
(id_client NUMBER(5) PRIMARY KEY,
nume
VARCHAR2(20),
id_adresa NUMBER(5) REFERENCES
adrese(id_adresa),
id_ang NUMBER(5),
CONSTRAINT clienti_fk FOREIGN KEY
(id_ang) REFERENCES
agenti_inchirieri (id_ang))
Slide 11
Dobândirea cunoștințelor
În cazul exemplului anterior am definit:
o constrângere referențială la nivel de
tabelă, care pune în legătură tabela clienti și
tabela agenti_inchirieri prin intermediul
câmpului de legătură id_ang, care este cheie
primară în tabela agenti_inchirieri și cheie
străină în tabela clienti
o constrângere referențială la nivel de
coloană, care leagă tabela clienti de tabela
adrese prin intermediul câmpului de legătură
id_adresa.
Slide 12
Dobândirea cunoștințelor
• La rularea exemplului anterior vom observa că se
va genera o eroare, deoarece tabela adrese și
tabela agenti_inchirieri au fost create fără a se
specifica cheia primară (la momentul creării
acelor tabele nu am avut cunoștințe despre
noțiunea de cheie primară).
• În momentul de față nu putem să creăm tabela
clienti împreună cu constrângerea de cheie
străină, acest lucru fiind posibil după ce vom
învăța cum se adaugă o constrângere, moment în
care vom putea să adăugăm constrângerile de
cheie primară în tabela adrese și tabela
agenti_inchierieri.
Slide 13
Dobândirea cunoștințelor
• La definirea unei chei
străine se poate utiliza o
clauză suplimentară ON
DELETE CASCADE care
precizează că la ştergerea
unei linii din tabela părinte
se vor şterge automat din
tabela copil acele linii care
fac referire la linia ce se
şterge din tabela părinte.
• Prin folosirea acestei opţiuni
în exemplul alăturat, la
ştergerea unei adrese se
vor şterge toţi clienţii de la
adresa respectivă.
Exemplu:
CREATE TABLE clienti
(id_client NUMBER(5) PRIMARY
KEY,
nume VARCHAR2(20),
id_adresa NUMBER(5)
REFERENCES
adrese(id_adresa)
ON DELETE CASCADE,
id_ang NUMBER(5),
CONSTRAINT clienti_fk FOREIGN
KEY (id_ang) REFERENCES
angajati(id_ang))
Slide 14
Dobândirea cunoștințelor
• O altă opţiune este ON
DELETE SET NULL care
face ca la ştergerea unei
tabele părinte, valorile
cheii străine din liniile
tabelei copil care fac
referire la linia ştearsă vor
fi setate pe NULL.
• În exemplul alăturat la
ştergerea unei adrese, în
dreptul adresei clienţilor
care au avut acea
adresă va apărea
valoarea NULL.
Exemplu:
CREATE TABLE clienti
(id_client NUMBER(5),
nume VARCHAR2(20),
id_adresa NUMBER(5)
REFERENCES
adrese(id_adresa) ON
DELETE SET NULL,
id_ang NUMBER(5),
CONSTRAINT clienti_fk
FOREIGN KEY (id_ang)
REFERENCES
angajati(id_ang))
Slide 15
Dobândirea cunoștințelor
Constrângerea de verificare (CHECK)
• Constrângerea CHECK
defineşte explicit o condiţie
pe care fiecare linie trebuie
să o satisfacă .
• Sintaxa:
[CONSTRAINT nume constrangere]
CHECK (conditie)
Exemplu:
CREATE TABLE imbunatatiri
( id_imbunatatiri NUMBER(3) PRIMARY KEY,
mobilat VARCHAR2(2),
termopane VARCHAR2(2),
aer_conditionat VARCHAR2(2),
parchet VARCHAR2(2),
gresie VARCHAR2(2),
faianta VARCHAR2(2),
telefon VARCHAR2(2),
cablu VARCHAR2(2),
internet VARCHAR2(2),
gaz VARCHAR2(2) CHECK (gaz='Da'),
apometre VARCHAR2(2))
În exemplul de mai sus câmpul gaz va avea
valoarea “Da”.
Slide 16
Dobândirea cunoștințelor
Observați greșelile efectuate la definirea constrângerilor în
exemplul de mai jos și comentați-le:
Definirea constrângerilor
Slide 2
Obiective
În această lecție veți învăța :
• Să definiți și descrieți constrângerile
• Să definiți constrângerile la nivel de tabelă
• Să definiți constrângerile la nivel de
coloană
Slide 3
Dobândirea cunoștințelor
Așa cum am precizat în partea de
proiectare a bazelor de date, orice bază de
date trebuie să stabilească regulile de
integritate care să garanteze că datele
introduse sunt corecte și valide. Asta
înseamnă că dacă există o regulă sau
restricție asupra unei entități, atunci datele
introduse în baza de date respectă aceste
restricții.
Slide 4
Dobândirea cunoștințelor
• Regulile de integritate se definesc la
crearea tabelelor folosind constrângerile.
• În funcţie de nivelul la care sunt definite
avem:
- constrângeri la nivel de tabelă care pot
acţiona asupra unei combinaţii de coloane
- constrângeri la nivel de coloană
Slide 5
Dobândirea cunoștințelor
• Constrângerile pot fi clasificate în:
- constrângeri de domeniu, care definesc
valorile pe care le poate lua un atribut ( NOT
NULL, UNIQUE, CHECK)
- constrângeri de integritate a tabelei, precizând
cheia primară a acesteia
- constrângeri de integritate referenţială, care
asigură coerenţa între cheile primare şi
cheile străine corespunzătoare
Slide 6
Dobândirea cunoștințelor
• Orice constrângere într-o bază de date are un nume.
Numele îl putem da noi la crearea constrângerii , ca
de exemplu clienti_id_client_pk , sau nu și în acest
caz sistemul denumește constrângerea, ca de
exemplu SYS_C00585417.
• Un mod de a denumi o constrângere este o
combinație între numele tabelei, numele coloanei la
care se referă și tipul constrângerii(uk, nn, ck, pk, fk):
nume-tabelă_nume-coloană_tipul-contrângerii
• Este recomandat să denumim noi constrângerile
deoarece numele dat de sistem este greu de regăsit.
Slide 7
Dobândirea cunoștințelor
Constrângerea NOT NULL
Exemplu:
• Necompletarea în tabelă a unei
celule conduce la completarea ei cu
valoarea NULL, semnificând faptul că
celula respectivă are de fapt o
valoare nedefinită.
• Coloanele dintr-o tabelă
corespunzătoare atributelor obligatorii
din ERD trebuie obligatoriu
completate, ele nu pot conţine
valoarea NULL.
• Pentru definirea acestei
restricţii se foloseşte restricţia NOT
NULL.
• Constrângerile NOT NULL se pot
defini doar la nivel de coloană.
• Restricţia NOT NULL poate fi folosită
împreună cu clauza DEFAULT.
• În exemplul de mai jos valoarea
câmpului nume nu poate fi NULL,
deoarece se va genera o eroare.
CREATE TABLE agenti_inchirieri
(id_ang NUMBER(5),
nume VARCHAR2(20) NOT NULL,
data_ang DATE DEFAULT SYSDATE,
salariu NUMBER(5),
id_adresa NUMBER(5),
id_sef NUMBER(5),
data_nast DATE,
permis VARCHAR2(5),
comision NUMBER(5))
Slide 8
Dobândirea cunoștințelor
Constrângerea UNIQUE
Exemplu:
CREATE TABLE altii
• Aceasta desemnează o coloană sau o
combinaţie de coloane ca o cheie unică.
(id_ang NUMBER(5),
• Două linii în aceeaşi tabelă nu pot avea
nume VARCHAR2(20),
aceeaşi valoare pentru această cheie.
• Coloanele definite ca UNIQUE pot
data_ang DATE,
conţine valori NULL, iar acestea pot fi
oricâte, adică valoarea NULL este singura salariu NUMBER(5),
valoare ce poate fi duplicată într-o coloană id_adresa NUMBER(5) UNIQUE,
UNIQUE, alte valori nu pot fi duplicate.
• Sintaxa constrângerii la nivel de tabelă: id_sef NUMBER(5),
[CONSTRAINT nume constrangere]
functia VARCHAR2(20) ,
UNIQUE (Coloana, Coloana, ...)
CONSTRAINT altii_uk UNIQUE
• Sintaxa constrângerii la nivel de coloană:
(nume, data_ang))
[CONSTRAINT nume constrangere]
UNIQUE
Slide 9
Dobândirea cunoștințelor
Constrângere de cheie primară
• Ca şi la cheile unice, o cheie primară
forţează unicitatea unei coloane sau
combinaţii de coloane implicate şi un
index unic este creat pentru a conduce
aceasta.
• NULL-urile nu sunt permise în coloanele
de chei primare.
• Sintaxa constrângerii la nivel de tabelă :
[CONSTRAINT nume constrangere]
PRIMARY KEY (Coloana, Coloana, ...)
• Sintaxa constrângerii la nivel de coloană :
[CONSTRAINT nume constrangere]
PRIMARY KEY
• Aceeaşi combinaţie de coloane nu poate
fi folosită şi pentru o cheie primară şi
pentru una unică.
Exemplu:
CREATE TABLE inchirieri
( id_locuinta NUMBER(5),
id_client NUMBER(5),
data_preluarii DATE,
data_predarii DATE,
CONSTRAINT inchirieri_pk PRIMARY
KEY(id_locuinta, id_client))
În exemplul de mai sus s-a definit
o constrângere de cheie
primară la nivel de tabelă, iar
cheia primară este formată din
două câmpuri id_locuinta și
id_client.
Slide 10
Dobândirea cunoștințelor
Constrângere de cheie străină
• Cheile străine furnizează reguli de integritate
de referinţă înăuntrul unei tabele sau între
tabele.
• O cheie străină este folosită oriunde într-o
relaţie cu fiecare cheie primară sau unică.
• Sintaxa constrângerii la nivel de tabelă:
[CONSTRAINT nume constrangere]
FOREIGN KEY (Coloana, Coloana, ...)
REFERENCES tabela (Coloana, Coloana,...)
• Sintaxa constrângerii la nivel de coloană:
[CONSTRAINT nume constrangere]
REFERENCES tabela (Coloana)
• Cuvintele 'FOREIGN KEY' nu sunt folosite în
cazul constrângerii la nivel de coloană a
sintaxei.
Exemplu:
CREATE TABLE clienti
(id_client NUMBER(5) PRIMARY KEY,
nume
VARCHAR2(20),
id_adresa NUMBER(5) REFERENCES
adrese(id_adresa),
id_ang NUMBER(5),
CONSTRAINT clienti_fk FOREIGN KEY
(id_ang) REFERENCES
agenti_inchirieri (id_ang))
Slide 11
Dobândirea cunoștințelor
În cazul exemplului anterior am definit:
o constrângere referențială la nivel de
tabelă, care pune în legătură tabela clienti și
tabela agenti_inchirieri prin intermediul
câmpului de legătură id_ang, care este cheie
primară în tabela agenti_inchirieri și cheie
străină în tabela clienti
o constrângere referențială la nivel de
coloană, care leagă tabela clienti de tabela
adrese prin intermediul câmpului de legătură
id_adresa.
Slide 12
Dobândirea cunoștințelor
• La rularea exemplului anterior vom observa că se
va genera o eroare, deoarece tabela adrese și
tabela agenti_inchirieri au fost create fără a se
specifica cheia primară (la momentul creării
acelor tabele nu am avut cunoștințe despre
noțiunea de cheie primară).
• În momentul de față nu putem să creăm tabela
clienti împreună cu constrângerea de cheie
străină, acest lucru fiind posibil după ce vom
învăța cum se adaugă o constrângere, moment în
care vom putea să adăugăm constrângerile de
cheie primară în tabela adrese și tabela
agenti_inchierieri.
Slide 13
Dobândirea cunoștințelor
• La definirea unei chei
străine se poate utiliza o
clauză suplimentară ON
DELETE CASCADE care
precizează că la ştergerea
unei linii din tabela părinte
se vor şterge automat din
tabela copil acele linii care
fac referire la linia ce se
şterge din tabela părinte.
• Prin folosirea acestei opţiuni
în exemplul alăturat, la
ştergerea unei adrese se
vor şterge toţi clienţii de la
adresa respectivă.
Exemplu:
CREATE TABLE clienti
(id_client NUMBER(5) PRIMARY
KEY,
nume VARCHAR2(20),
id_adresa NUMBER(5)
REFERENCES
adrese(id_adresa)
ON DELETE CASCADE,
id_ang NUMBER(5),
CONSTRAINT clienti_fk FOREIGN
KEY (id_ang) REFERENCES
angajati(id_ang))
Slide 14
Dobândirea cunoștințelor
• O altă opţiune este ON
DELETE SET NULL care
face ca la ştergerea unei
tabele părinte, valorile
cheii străine din liniile
tabelei copil care fac
referire la linia ştearsă vor
fi setate pe NULL.
• În exemplul alăturat la
ştergerea unei adrese, în
dreptul adresei clienţilor
care au avut acea
adresă va apărea
valoarea NULL.
Exemplu:
CREATE TABLE clienti
(id_client NUMBER(5),
nume VARCHAR2(20),
id_adresa NUMBER(5)
REFERENCES
adrese(id_adresa) ON
DELETE SET NULL,
id_ang NUMBER(5),
CONSTRAINT clienti_fk
FOREIGN KEY (id_ang)
REFERENCES
angajati(id_ang))
Slide 15
Dobândirea cunoștințelor
Constrângerea de verificare (CHECK)
• Constrângerea CHECK
defineşte explicit o condiţie
pe care fiecare linie trebuie
să o satisfacă .
• Sintaxa:
[CONSTRAINT nume constrangere]
CHECK (conditie)
Exemplu:
CREATE TABLE imbunatatiri
( id_imbunatatiri NUMBER(3) PRIMARY KEY,
mobilat VARCHAR2(2),
termopane VARCHAR2(2),
aer_conditionat VARCHAR2(2),
parchet VARCHAR2(2),
gresie VARCHAR2(2),
faianta VARCHAR2(2),
telefon VARCHAR2(2),
cablu VARCHAR2(2),
internet VARCHAR2(2),
gaz VARCHAR2(2) CHECK (gaz='Da'),
apometre VARCHAR2(2))
În exemplul de mai sus câmpul gaz va avea
valoarea “Da”.
Slide 16
Dobândirea cunoștințelor
Observați greșelile efectuate la definirea constrângerilor în
exemplul de mai jos și comentați-le: