CONSTRING: integritatea bazei de date

Controlul integrității bazei de date

Un alt lucru important de știut despre bazele de date este CONSTRAINS. Cu ajutorul constrângerilor, puteți controla modificările datelor din tabelele dvs. și puteți menține integritatea și consistența acestora.

Ce este consistența datelor atunci când vorbim despre o bază de date?

Să luăm magazinul nostru online cu tabele pentru angajați, produse și sarcini . Știm deja că în tabelul de activități pot exista sarcini care nu sunt atribuite nimănui: employee_id -ul acestor rânduri este NULL.

Dar ce se întâmplă dacă există o intrare în tabelul de activități cu un employee_id egal cu, să zicem, 115? La urma urmei, nu avem un astfel de angajat. Nu avem un angajat cu id = 115 în tabelul de angajați. În același timp, un link către un angajat cu acest id este în tabelul de activități. Acesta este un exemplu de inconsecvență a datelor .

Deci, cum reconciliem aceste date? În mod ideal, ar fi astfel ca serverul SQL, cu orice modificare a datelor, să controleze toate aceste nuanțe. Și există o astfel de oportunitate, se numește FOREIGN_KEY.

Dacă o coloană din tabelul dvs. conține nu doar numere, ci și rânduri de ID dintr-un alt tabel, atunci acest lucru poate fi specificat în mod explicit.

Adăugarea unei CHEIE STRĂINE

O astfel de cheie poate fi adăugată la tabel atât în ​​etapa creării sale, cât și după, folosind ALTER TABLE. Formatul nu este fundamental diferit. Vom prezenta ambele variante.

Forma generală a unei astfel de chei/reguli este:

FOREIGN KEY (column)
  	REFERENCES table(column)

Să adăugăm această cheie/regulă la tabelul de sarcini pentru a ne asigura că toate employee_id-urile din tabel se referă la o intrare existentă în tabelul de angajați. Acest script va arăta astfel:

ALTER TABLE task
      ADD FOREIGN KEY (employee_id)
  	REFERENCES employee(id)

Și dacă am decide să adăugăm această regulă în momentul creării tabelului de activități, atunci codul ar arăta astfel:

CREATE TABLE task (
      id INT,
      name VARCHAR(100),
      employee_id INT,
      deadline DATE,
 
      PRIMARY KEY (id),
  	  FOREIGN KEY (employee_id)  
	      REFERENCES employee(id)
);

Apropo, există situații în care șirul la care ne referim are o cheie compusă unică: de exemplu, „Nume și anul nașterii” sau „productCatogoryId și productId”. Atunci FORIGN KEY poate fi scris astfel:

FOREIGN KEY (our_column1, our_column2)
  	REFERENCES table(their_column1, their_column2)

CHEIE STRĂINĂ și modificarea datelor

Acum imaginați-vă o situație în care am decis să actualizăm unele date din tabelul de angajați și id-ul de angajat s-a schimbat. Ce se va întâmpla cu datele din tabelul de sarcini? Așa e, vor deveni irelevante, iar integritatea bazei noastre de date va fi încălcată.

Pentru a preveni acest lucru, îi puteți spune SQL Server să modifice employee_id-ul tuturor rândurilor din toate tabelele care se referă la acest id modificat atunci când id-ul din tabelul angajat se schimbă.

Astfel de scripturi se numesc OnUpdate și OnDelete . Ce trebuie să faceți dacă ID-ul înregistrării se modifică și ce să faceți dacă înregistrarea este ștearsă?

Odată cu eliminarea, nu totul este atât de simplu. Dacă aveți obiecte dependente reprezentate prin șiruri în baza de date care se referă unul la altul, atunci este posibilă o mare varietate de scenarii de comportament atunci când ștergeți un obiect.

Să presupunem că ștergem un utilizator de site, ceea ce înseamnă că trebuie să ștergem toată corespondența lui personală. Dar este puțin probabil să eliminăm toate comentariile sale publice.

Sau un angajat renunță. Ar fi ciudat dacă ar renunța și, în același timp, toate sarcinile care i-au fost atribuite ar dispărea din baza de date. Dar dacă ar fi rămas numiți nu de el, ar fi ieșit și prost. Este mai corect să se facă astfel încât angajatul să poată renunța după ce și-a reatribuit toate sarcinile altor persoane.

Iată cum putem descrie aceste scenarii folosind FOREIGN KEY. Forma generală a unei astfel de chei/reguli este:

FOREIGN KEY (column)
  	REFERENCES table(column)
 	[ON DELETE reference_option]
 	[ON UPDATE reference_option]

Ce trebuie făcut în cazul ștergerii (ON DELETE) sau modificării (ON UPDATE) înregistrărilor? În total, pot exista 5 opțiuni pentru ca serverul SQL să acționeze în fiecare dintre aceste situații:

Iată cum am putea modifica tabelul de sarcini:

ALTER TABLE task
  	ADD FOREIGN KEY (employee_id)
  	REFERENCES employee(id)
  	ON UPDATE CASCADE
  	ON DELETE RESTRICT;

Ce scrie aici:

ON UPDATE CASCADE : Dacă cheia id din tabelul de angajați se modifică, atunci schimbați și employee_id din tabelul de activități care face referire la acesta.

ON DELETE RESTRICT : Dacă un rând este șters din tabelul de angajați și este referit din tabelul de activități, atunci împiedicați ștergerea rândului din tabelul de angajați.