Pentru orice afacere, datele sunt esențiale, iar gestionarea lor eficientă reprezintă un avantaj competitiv. Utilizarea bazelor de date este crucială pentru stocarea și manipularea informațiilor.
Bazele de date sunt structurate conform anumitor reguli, permițând organizarea datelor în conexiuni logice. Bazele de date relaționale, populare încă din anii ’70, rămân preferatele datorită eficienței lor în gestionarea datelor complexe.
Deși există numeroase soluții relaționale, MySQL se remarcă, fiind a doua cea mai folosită bază de date la nivel mondial, conform statisticilor din ianuarie 2022.
În serverele SQL, constrângerile sunt reguli prestabilite care limitează valorile introduse în coloane. Aceste constrângeri, aplicate la nivel de coloană sau grup de coloane, sunt fundamentale pentru integritatea, acuratețea și fiabilitatea datelor.
Simplu spus, doar datele care respectă constrângerea sunt acceptate. În caz contrar, operațiunea de inserare eșuează.
Acest articol presupune familiaritatea cu bazele de date relaționale, în special MySQL, și își propune să aprofundeze cunoștințele despre constrângeri, cu accent pe cheile străine.
Constrângerile cheie primară – O analiză succintă
Într-un tabel SQL, cheia primară (PK) identifică în mod unic fiecare rând. Aceasta poate fi formată dintr-o singură coloană sau mai multe, asigurând integritatea tabelului și unicitatea datelor.
La definirea unei chei primare, sistemul de baze de date creează automat indecși unici, care accelerează accesul la date. Cheile primare sunt, așadar, esențiale pentru optimizarea interogărilor.
Când o cheie primară este formată din mai multe coloane, aceasta este numită cheie compusă. În acest caz, fiecare coloană poate avea valori duplicate, dar combinația valorilor din toate coloanele trebuie să fie unică.
De exemplu, într-un tabel cu coloanele „id”, „nume” și „vârstă”, cheia primară compusă definită pe „id” și „nume” permite valori duplicate pentru fiecare dintre acestea individual, dar nu și pentru combinația lor. Astfel, pot exista înregistrări cu `id=1` și `name=Walter`, precum și `id=1` și `name=Henry`, dar nu se poate repeta combinația `id=1` și `name=Walter`.
Iată câteva aspecte importante:
- Un tabel poate avea o singură constrângere de cheie primară.
- Cheile primare nu pot conține mai mult de 16 coloane și o lungime maximă de 900 de caractere.
- Indecșii generați de cheile primare pot îmbunătăți performanța tabelului. Un tabel poate avea maximum un index grupat și 999 indecși negrupați.
- Dacă tipul indecșilor (grupat/negrupat) nu este specificat, se utilizează cel grupat implicit.
- Toate coloanele unei chei primare trebuie să aibă constrângerea NOT NULL. Dacă nu sunt declarate astfel, se va seta automat această constrângere.
- Pentru coloanele de tip CLR (Common Language Runtime), cheile primare necesită ca tipul de date să suporte ordonarea binară.
Constrângerile cheie străine – O prezentare succintă
Cheia străină (FK) este o coloană sau un grup de coloane care leagă două tabele. Ea gestionează datele stocate într-un tabel, făcând referire la cheia primară a altui tabel.
Referința unei chei străine implică legarea a două tabele, unde o coloană sau un grup de coloane dintr-un tabel fac trimitere la cheia primară a altuia.
Acest proces creează o relație între tabele, stabilind o legătură logică bazată pe coloanele care stochează cheile primare.
Un exemplu practic este legătura dintre tabelul `Sales.SalesOrderHeader` (care stochează anteturile comenzilor) și tabelul `Vânzări.Persoană` (care stochează informații despre vânzători). Între cele două tabele există o relație directă: fiecare comandă este asociată unui vânzător.
În acest caz, `SalesPersonID` din tabelul `SalesOrderHeader` se referă la coloana cheie primară din tabelul `SalesPerson`. `SalesPersonID` din `SalesOrderHeader` devine cheia străină.
Relația impune regula conform căreia o valoare `SalesPersonID` nu poate exista în `SalesOrderHeader` dacă nu este definită în tabelul `SalesPerson`.
Un tabel poate referi până la 253 de alte tabele sau coloane ca și chei străine (referințe de ieșire). Începând cu 2016, serverul SQL a mărit numărul de referințe de intrare la 10000, dar cu unele limitări:
- Referințele cheilor externe de peste 253 sunt permise doar pentru operațiunile DELETE DML. Operațiunile MERGE și UPDATE nu sunt acceptate.
- Tabelele cu referințe la propria lor cheie străină sunt limitate la maximum 253 de referințe.
- Pentru indexurile de stocare în coloane, tabelele optimizate pentru memorie și tabelele cu chei externe partiționate, numărul referințelor este limitat la 253.
Care sunt avantajele cheilor externe?
Constrângerile de cheie străină sunt esențiale pentru menținerea integrității și coerenței datelor într-o bază de date relațională. Iată o prezentare a avantajelor lor:
- Integritate referențială: Asigură corespondența dintre datele din tabelele relaționate, menținând consistența informațiilor.
- Prevenirea înregistrărilor orfane: La ștergerea unui rând dintr-un tabel părinte, constrângerile de cheie străină pot impune ștergerea înregistrărilor corespunzătoare din tabelele copil, evitând astfel inconsistentele.
- Performanță îmbunătățită: Permite sistemului de baze de date să optimizeze interogările, valorificând relațiile dintre tabele.
Indecșii cheilor externe
Spre deosebire de cheile primare, constrângerile de cheie străină nu generează automat indecși corespunzători. Crearea manuală a indecșilor pentru aceste coloane poate fi benefică datorită:
- Coloanele cu cheie străină sunt adesea folosite în operațiile de tip JOIN, pentru a lega date din tabele relaționate. Indecșii ajută baza de date să acceseze mai rapid datele din tabelul străin.
- La modificarea constrângerilor cheii primare, se efectuează verificări și asupra cheilor străine din tabelele relaționate.
Crearea indecșilor nu este obligatorie. Se pot combina date din două tabele chiar și fără definirea cheilor primare și externe, dar adăugarea acestor constrângeri optimizează tabelele, îmbunătățind eficiența interogărilor. În plus, se verifică legăturile dintre cheile primare și externe la modificarea unora dintre acestea.
Sfaturi pentru crearea constrângerilor de cheie străină în SQL
După ce am înțeles rolul cheilor externe, să ne concentrăm pe aspectele practice ale creării lor.
O coloană „Cheie străină” dintr-un tabel face referire la „Cheia primară” a altui tabel. Tabelul cu cheia primară este tabelul părinte, iar cel cu cheia străină este tabelul copil.
Crearea unei chei externe la crearea unui tabel
Când se creează un tabel, se poate adăuga și o constrângere de cheie străină pentru a menține integritatea referențială, după cum urmează:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
Codul creează un tabel „comenzi” cu o cheie primară `order_id`, o coloană `customer_id` și o dată `order_date`. Constrângerea FOREIGN KEY este adăugată pe coloana `customer_id`, făcând referire la coloana `customer_id` din tabelul `clienți`.
Crearea unei chei externe după crearea unui tabel
Dacă se dorește adăugarea unei chei externe la un tabel existent, se folosește instrucțiunea `ALTER TABLE`, după cum urmează:
ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
În acest caz, se adaugă o cheie străină `customer_id` în tabelul `comenzi`, făcând referire la coloana `customer_id` din tabelul `clienți`.
Crearea unei chei străine fără a valida datele existente
La adăugarea unei chei externe, baza de date validează automat datele existente. Dacă se știe că acestea sunt deja consistente, se poate sări peste această validare, astfel:
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) NOT VALIDATE;
Comanda `NOT VALIDATE` instruiește baza de date să nu verifice datele existente. Această opțiune este utilă în anumite scenarii, cum ar fi la gestionarea unor volume mari de date.
Crearea unei chei externe cu ajutorul operațiilor DELETE/UPDATE
La crearea constrângerilor de cheie străină, se poate defini acțiunea care va fi aplicată în cazul actualizării sau ștergerii unui rând referit. Se folosesc constrângeri de integritate referențială în cascadă pentru a dicta aceste acțiuni, precum:
#1. FĂRĂ ACȚIUNE
Regula „FĂRĂ ACȚIUNE” este comportamentul implicit la crearea unei chei externe. Aceasta înseamnă că, dacă se încearcă ștergerea sau actualizarea unui rând referit, nu se va realiza nicio acțiune.
Motorul bazei de date va genera o eroare dacă se încalcă constrângerea de cheie străină. Totuși, această variantă nu este recomandată, deoarece poate duce la probleme de integritate referențială. Iată un exemplu:
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE NO ACTION ON UPDATE NO ACTION;
#2. CASCADĂ
Regula „CASCADĂ” dictează că, la actualizarea sau ștergerea unui rând dintr-un tabel părinte, rândurile corespunzătoare din tabelele copil vor fi actualizate sau șterse automat. Această opțiune este foarte eficientă în menținerea integrității referențiale, dar trebuie folosită cu precauție.
Iată un exemplu:
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE ON UPDATE CASCADE;
Trebuie evitată ștergerea accidentală a prea multor date sau crearea referințelor circulare. Utilizați această opțiune doar dacă este necesar și cu multă atenție.
Există câteva reguli pentru utilizarea `CASCADE`:
- Nu se poate utiliza `CASCADE` dacă o coloană `timestamp` face parte din cheia externă sau din cheia referită.
- Dacă tabelul are un trigger `INSTEAD OF DELETE`, nu se poate specifica `ON DELETE CASCADE`.
- Nu se poate specifica `ON UPDATE CASCADE` dacă tabelul are un trigger `INSTEAD OF UPDATE`.
#3. SET NULL
În cazul în care un rând este șters sau actualizat dintr-un tabel părinte, toate valorile corespunzătoare din cheia străină sunt setate la valoarea NULL. Această regulă presupune că respectivele coloane pot conține valoarea `NULL` și nu poate fi specificată pentru tabele care au triggeri `INSTEAD OF UPDATE`.
Iată un exemplu:
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE SET NULL ON UPDATE SET NULL
În acest caz, coloana cheii străine `customer_id` din tabelul `comenzi` va fi setată la `NULL` dacă rândul corespunzător din tabelul `clienți` este șters sau actualizat.
#4. SET DEFAULT
În această variantă, valorile cheii străine sunt setate la valoarea implicită dacă rândul din tabelul părinte este actualizat sau șters.
Această constrângere este aplicată doar dacă toate coloanele cheii străine au o valoare implicită. Dacă o coloană poate accepta valori `NULL`, valoarea implicită va fi setată la `NULL`. Această opțiune nu poate fi folosită pentru tabelele care au triggeri `INSTEAD OF UPDATE`. Iată un exemplu:
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
În exemplul de mai sus, coloana `customer_id` din tabelul `comenzi` va fi setată la valoarea implicită, atunci când rândul corespunzător din tabelul `clienți` este șters sau actualizat.
Concluzie
Acest ghid a oferit o recapitulare a constrângerilor de cheie primară și o analiză detaliată a constrângerilor de cheie străină. S-au prezentat mai multe tehnici pentru crearea acestora. Deși există mai multe moduri de a crea constrângeri de cheie străină, acest articol a acoperit cele mai importante.
Nu vă limitați doar la folosirea individuală a acestor tehnici. Metodele de constrângere `CASCADE`, `SET NULL`, `SET DEFAULT` și `NO ACTION` pot fi combinate în tabele cu relații referențiale.
Dacă un tabel întâlnește `NO ACTION`, acesta va continua la următoarea regulă. În alte cazuri, o acțiune `DELETE` poate declanșa o combinație a acestor reguli, `NO ACTION` fiind executată ultima.
Pentru aprofundarea cunoștințelor, vă recomandăm să consultați un cheat sheet SQL.