KOSZTRÁCIÓ: az adatbázis integritása

Adatbázis integritásának ellenőrzése

Egy másik fontos tudnivaló az adatbázisokról a CONSTRAINS. A megszorítások segítségével ellenőrizheti a táblák adatváltozásait, és megőrizheti azok integritását és konzisztenciáját.

Mit jelent az adatok konzisztenciája , ha adatbázisról beszélünk?

Vegyük online áruházunkat alkalmazotti, termék- és feladattáblázatokkal . Azt már tudjuk, hogy a feladattáblában lehetnek olyan feladatok, amelyek nincsenek hozzárendelve senkihez: az ilyen sorok munkavállalói_azonosítója NULL.

De mi történik, ha a feladattáblázatban van egy bejegyzés, amelynek alkalmazotti_azonosítója mondjuk 115? Hiszen nálunk nincs ilyen alkalmazott. Nincs olyan alkalmazottunk, akinek azonosítója = 115 az alkalmazotti táblában. Ugyanakkor egy ezzel az azonosítóval rendelkező alkalmazottra mutató hivatkozás található a feladattáblázatban. Ez egy példa az adatok következetlenségére .

Tehát hogyan egyeztetjük ezeket az adatokat? Ideális esetben az SQL-szerver minden adatmódosítással ellenőrizné ezeket az árnyalatokat. És van egy ilyen lehetőség, a neve FOREIGN_KEY.

Ha a táblázat egy oszlopa nem csak számokat tartalmaz, hanem egy másik tábla azonosító sorait is, akkor ez kifejezetten megadható.

IDEGEN KULCS hozzáadása

Egy ilyen kulcs hozzáadható a táblához a létrehozásának szakaszában és utána is, az ALTER TABLE használatával. A formátum alapvetően nem különbözik egymástól. Mindkét lehetőséget bemutatjuk.

Az ilyen kulcs/szabály általános formája a következő:

FOREIGN KEY (column)
  	REFERENCES table(column)

Adjuk hozzá ezt a kulcsot/szabályt a feladattáblához, hogy biztosítsuk, hogy a táblában szereplő összes alkalmazott_azonosító az alkalmazotti tábla egy meglévő bejegyzésére hivatkozzon. Ez a szkript így fog kinézni:

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

És ha úgy döntöttünk, hogy hozzáadjuk ezt a szabályt a feladattábla létrehozásakor, akkor a kód így néz ki:

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

Egyébként vannak olyan helyzetek, amikor az általunk hivatkozott karakterláncnak egyedi összetett kulcsa van: például „Név és születési év” vagy „productCatogoryId and productId”. Ekkor az IDEGEN KULCS így írható:

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

IDEGEN KULCS és változó adatok

Most képzeljünk el egy olyan helyzetet, amikor úgy döntöttünk, hogy frissítünk néhány adatot az alkalmazotti táblában, és megváltozott az alkalmazotti azonosítónk. Mi lesz a feladattábla adataival? Így van, irrelevánssá válnak, és sérül az adatbázisunk integritása.

Ennek elkerülése érdekében megkérheti az SQL Servert, hogy módosítsa a munkavállalói azonosítót az összes olyan táblában, amely erre a módosított azonosítóra hivatkozik, amikor az alkalmazotti tábla azonosítója megváltozik.

Az ilyen szkriptek neve OnUpdate és OnDelete . Mi a teendő, ha a rekord azonosítója megváltozik, és mi a teendő, ha a rekord törlődik?

Az eltávolítással nem minden olyan egyszerű. Ha az adatbázisban egymásra hivatkozó karakterláncokkal ábrázolt függő objektumai vannak, akkor egy objektum törlésekor sokféle viselkedési forgatókönyv lehetséges.

Tegyük fel, hogy törölünk egy webhely felhasználót, ami azt jelenti, hogy törölnünk kell az összes személyes levelezését. De nem valószínű, hogy el kell távolítanunk az összes nyilvános megjegyzését.

Vagy egy alkalmazott felmond. Furcsa lenne, ha kilépne, és ezzel egyidejűleg a rábízott összes feladat eltűnne az adatbázisból. De ha nem ő nevezte volna ki őket, az is rosszul alakult volna. Helyesebb úgy tenni, hogy a munkavállaló felmondhasson, miután minden feladatát átruházta másokra.

Így írhatjuk le ezeket a forgatókönyveket a FOREIGN KEY használatával. Az ilyen kulcs/szabály általános formája a következő:

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

Mi a teendő rekordok törlése (ON DELETE) vagy megváltoztatása (ON UPDATE) esetén? Összességében 5 lehetőség áll rendelkezésre az SQL-kiszolgálónak az alábbi helyzetek mindegyikében:

A következőképpen módosíthatjuk a feladattáblázatunkat:

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

Ami ide van írva:

UPDATE CASCADE ON : Ha az alkalmazotti táblában az id kulcs megváltozik, akkor módosítsa az arra hivatkozó feladattáblázatban az alkalmazott_id értéket is.

TÖRLÉS KORLÁTOZÁSÁNAK : Ha egy sort törlünk az alkalmazotti táblából, és a feladattáblából hivatkozunk rá, akkor akadályozza meg a sor törlését az alkalmazotti táblából.