7. fejezet
Megszorítások és triggerek Ebben a fejezetben az SQL-nek azokat a sajátosságait tekintjük át, amelyek az aktív elemek létrehozásával kapcsolatosak. Egy aktív elem olyan kifejezés vagy utasítás, amelyet egyszer megírunk, eltárolunk az adatbázisban, és azt várjuk el tole, hogy a megfelelo idopillanatokban lefusson. Ez az idopont lehet egy esemény bekövetkezése, mint például egy adott relációba való beszúrás, vagy lehet az adatbázisnak olyan megváltozása, amikor egy logikai értéku feltétel igazzá válik. Az egyik komoly nehézség, amivel az alkalmazásfejlesztok szembe találják magukat, hogy az adatbázis módosításakor az új információ nagyon sokféleképpen lehet hibás. Kézzel bevitt adatok esetén például gyakran fordul elo elírás vagy másolási hiba. Az alkalmazásokat megírhatjuk úgy is, hogy minden beszúrást, törlést és módosító utasítást a szükséges, helyességet biztosító ellenorzésekhez rendelünk hozzá. A legjobb ezeket az ellenorzéseket az adatbázisban tárolni, és az ABKR-re bízni az ellenorzést. Ekkor ugyanis biztosan nem felejtodik el egyetlen ellenorzés sem, sot így a felesleges munkát is elkerülhetjük. Az SQL számos lehetoséget kínál arra, hogy az épségi megszorításokat az adatbázisséma részeként adjuk meg. Ebben a fejezetben a legfontosabb módszereket fogjuk áttekinteni. Korábban már foglalkoztunk a kulcsmegszorításokkal, amelyeknél egy attribútum vagy egy attribútumhalmaz a reláció kulcsaként van megadva. Az SQL lehetové teszi az attribútumokra, a sorokra vonatkozó megszorításokat és a több relációt érinto megszorításokat is. Ez utóbbiakat önálló megszorításoknak nevezzük. Végül pedig a fejezet végén a "triggereket" fogjuk bemutatni. Ezek olyan aktív elemek, amelyek bizonyos események hatására jönnek muködésbe. Ilyen esemény lehet például egy adott relációba való beszúrás.
7.1. Kulcsok
és idegen
kulcsok
Emlékezzünk vissza a 2.3.6. alfejezetre, amelyben láthattuk, hogy az SQL lehetové teszi a PRIMARYKEY,illetve UNIQUEkulcsszó használatával azt, hogy egy vagy több attribútum a reláció kulcsa legyen. Az SQL nyelv a ,,kulcs" kifejezést
.......-
330
7. Megszorftások és triggerek
használja bizonyos hivatkozásiépség-megszorítások esetén is. Ezek a megszorítások, amelyeket idegenkulcs-megszorításoknak hívunk, azt mondják ki, hogy egy relációban eloforduló értéknek szerepelnie kell egy másik reláció elsodleges kulcsoszlopában vagy oszlopaiban is.
331
7.1. Kulcsok és idegen kulcsok FOREIGNKEY «attribútumok» REFERENCES
«attribútumok> ) 7.1. példa. Stúdió
Tegyük fel, hogy a következo relációt szeretnénk deklarálni: (név,
cím,
elnökAzon)
7.1.1. Idegen kulcsok megadása Az idegenkulcs-megszorítás bizonyos attribútumok értékeinek jelentésére eloírt követelmény. Vegyük például a 2.21. példát, amelyben azt néztük meg, hogyan fejezheto ki a relációs algebrában az a megszorítás, hogy a filmeknek a producer "azonosító szám" értéke néhány gyártásirányítónak is azonosító száma lesz a GyártásIrányító relációban. Az SQL-ben egy reláció azon attribútumát vagy attribútumait idegen kulcsnak deklarálhat juk, amelyek egy másik reláció (ez lehet akár ugyanaz a reláció is) bizonyos attribútumaira hivatkoznak. Ez a deklaráció két dolgot jelent egyszerre. 1. A másik reláció azon attribútumait, amelyekre hivatkozunk, elsodleges kulcsként vagy UNIQUEkulcsként kell deklarálni abban a relációban. Enélkül nem adhatjuk meg az idegenkulcs-deklarációt. 2. Az idegen kulcs értékeinek, amelyek elofordulnak az elso relációban, elo kell fordulniuk a hivatkozott attribútumokban is a másik reláció valamelyik sorában. Még pontosabban kifejezve, legyen F egy idegen kulcs, amely egy másik reláció G attribútumhalmazára hivatkozik. Tegyük fel, hogy az elso relációnak egy t sora az F attribútumaiban csupa nem NULLértékkel rendelkezik. Jelöljük t-nek ezen attribútumokon felvett értékeit t[F]-fel. Ekkor a hivatkozott relációnak kell hogy legyen olyan s sora, amelyik a G
attribútumainmegegyezikt[F]-fel,vagyiss[G]= t[F].
Ennek az elsodleges kulcsa a név, az elnökAzon amelyik a GyártásIrányító(név,
cím,
azonosító,
attribútuma
pedig idegen kulcs,
nettóBevétel)
reláció azonosító attribútumára hivatkozik. Az elnökAzon közvetlenül deklarálható az azonosító hivatkozással a következo módon:
attribútumra
történo
( CREATETABLE Stúdió név CHAR(30) PRIMARY KEY, cím VARCHAR(255), elnökAzon INT REFERENCES GyártásIrányító(azonosító) ); Vagy megadhatjuk
úgy is, hogy az idegen kulcsot külön deklaráljuk:
CREATETABLE Stúdió ( név CHAR(30) PRIMARY KEY, cím VARCHAR(255), elnökAzon INT, FOREIGN KEYelnökAzon REFERENCES GyártásIrányító(azonosító) );
.
Az idegen kulcsot is kétféleképpen deklarálhat juk ugyanúgy, ahogyan azt az elsodleges kulcsok esetében láttuk. a) Ha az idegen kulcs egyetlen attribútum, akkor az attribútum neve és típusa után adhatjuk meg, hogy az egy másik tábla egy attribútumára hivatkozik. (Annak az attribútumnak ott elsodleges kulcsnak vagy unique-nak kell lennie. ) A deklaráció formája a következo:
Figyeljük meg, hogy a hivatkozott azonosító attribútum a GyártásIrányító táblának valóban kulcsa, ahogyan az szükséges. Mindkét fenti deklarációnak az a jelentése, hogy ha egy érték szerepel a Stúdió tábla egy sorának elnökAzon oszlopában, akkor ennek az értéknek szerepelnie kell a GyártásIrányítótábla valamely sorának azonosító oszlopában is. Kivétel az az eset, ha a Stúdió tábla egy sorában az elnökAzon oszlopban NULLérték szerepel. Ilyenkor nem követelmény, hogy a NULLérték szerepeljen a másik tábla azonosító oszlopában. Valójában az azonosító elsodleges kulcs, és így nem is szerepelhet benne NULL érték. O
REFERENCES «attribútum» b) A másik lehetoség, hogya CREATETABLEutasításban az attribútumok listája után egy külön deklarációban adjuk meg, hogy bizonyos attribútumok idegen kulcsot alkotnak. Itt kell megadnunk azt a táblát és azokat az attribútumokat, amelyekre az idegen kulcs hivatkozik. (Ezeknek az attribútumoknak ez esetben is elsodleges vagy unique kulcsnak kell lenniük. ) A deklaráció formája ekkor a következo:
7.1.2. Hivatkozási
épség fenntartása
A séma tervezoje három lehetoség közül választhat az idegenkulcs-megszorítás kikényszerítésére. Az általános megközelítéseket megfigyelhettük a 7.1. példa vizsgálatánál, amelyben elvárás volt, hogy a Stúdióreláció elnökAzonértéke egyben a GyártásIrányító reláció azonosító értéke is legyen. A következo muveleteket az ABKR visszautasítja, vagyis egy futási hibát generál:
. 332
7. Megszorítások és triggerek
333
7.1. Kulcsok és idegen kulcsok
a) Ha megpróbálunk egy olyan sort beszúrni a Stúdió táblába, amelyben az elnökAzon értéke nem NULL,és nem egyezik meg a GyártásIrányító táblabeli azonosító-hoz tartozó egyik értékével sem.
7.2. példa.
b) Ha megpróbáljuk módosítani a Stúdió tábla egy sorát, és az elnökAzon attribútumot egy olyan nem NULLértékre változtatjuk, amelyik nem egyezik meg a GyártásIrányító táblabeli egyik azonosító értékkel sem.
reláció 7.1. példában szereplo deklarációját, hogy a
e) Ha megpróbálunk kitörölni egy sort a GyártásIrányító táblából, amelyben az azonosító értéke nem NULLés szerepel a Stúdió tábla elnökAzon oszlopában.
relációra vonatkozó törlések és módosítások esetén az általunk kívánt módszert alkalmazzaa rendszer. A 7.1. ábrán az említett példában szereplo elso CREATE TABLEutasítást láthatjuk az ONDELETEés ONUPDATEzáradékkal kiegészítve. Az 5. sor azt adja meg, hogy ha a GyártásIrányító táblából kitörlünk egy sort, akkor az összes Stúdió-beli sorban, amelyekben az éppen törölt gyártásirányító volt az elnök, az elnökAzon értékét NULL-rakell változtatni. A 6. sor azt mondja meg, hogy ha a GyártásIrányító tábla egy sorában megváltoztatjuk az azonosító-t, akkor a Stúdió tábla megfelelo soraiban ugyanarra az értékre kell változtatni az elnökAzon értékét.
d) Ha megpróbáljuk módosítani a GyártásIrányító tábla egy sorát oly módon, hogy az azonosító értékét is megváltoztatjuk és a régi azonosító értéke szerepel a Stúdió tábla elnökAzon oszlopában. Az elso két módosítás esetén, amelyek az idegenkulcs-megszorítással deklarált relációt akarták megváltoztatni, nincs választási lehetoségünk. A rendszer egyszeruen visszautasítja a megszorítást sérto módosítást. A hivatkozott relációra vonatkozó módosításoknál (vagyis az utolsó kettonél) viszont a tervezo három lehetoség közül választhat: 1. Alapértelmezés szerinti eljárás (Megszorítást sérto módosítások visszautasítása). Az SQL-ben az alapértelmezés szerinti eljárás minden olyan módosítást, amely megsérti a hivatkozásiépség-megszorítást, visszautasít. 2. Továbbgyuruzo eljárás. Ezzel az eljárással az idegen kulcsok hivatkozott attribútuma(i) is megváltoznak. Ha például a továbbgyuruzo eljárással egy stúdió elnökéhez tartozó GyártásIrányító sort törlünk, akkor a rendszer a hivatkozási épség megorzéséhez a Stúdió reláció hivatkozott sorait is törli. Ha cI-rol C2-re változtatjuk egy gyártásirányító azonosítóját, és van olyan sora a Stúdió táblának, amelyben az elnökAzon értéke CI, akkor a rendszer ezt az értéket is C2-remódosítja. 3. A NULLértékre állítás módszere. Itt a hivatkozott reláció egy idegen kulcsának értékét érinto módosításánál a hivatkozott reláció megfelelo értékeit NULLértékre változtatjuk. Ha például a GyártásIrányító reláció az egyik stúdió elnökéhez tartozó sorát töröljük, akkor a rendszer a szóban forgó stúdió elnökAzon értékeit NULLértékre kell állítsa. Ha ennek az elnöknek az azonosító számát a GyártásIrányító relációban megváltoztatjuk, akkor a Stúdió reláció elnökAzon értékét NULLértékre kell változtassuk. A fenti módszerek közül az alkalmazni kívántat a törlés és módosítás esetére külön-külön, egymástól függetlenül megadhatjuk, amikor az idegen kulcsot deklaráljuk. A megadásuk úgy történik, hogy a törlés esetén használandó módszert az ONDELETE,a módosítás esetén alkalmazandót pedig az ONUPDATEkulcsszó után adjuk meg. A módszerekre vonatkozó kulcsszavak pedig NULLértékre állítás esetén SET NULL,továbbgyuruzo módszer esetén pedig CASCADE.
Nézzük meg, hogyan kell módosítanunk a
Stúdió(név,
Gyártás
1) 2) 3) 4) 5)
cím, elnökAzon)
Irányító
(név,
cím,
azonosító,
nettóBevétel)
CREATE TABLE Stúdió ( név CHAR(30) PRIMARY KEY, cím VARCHAR(255), elnökAzon INT REFERENCES GyártásIrányító(azonosító) ON DELETE SET NULL
6)
ON UPDATE CASCADE ); 7.1. ábra.
Módszer megadása a hivatkozási épség megorzésére
Figyeljük meg, hogy a példában a törlés esetén a nullértékre állítás tunik értelmes megoldásnak, míg a módosítás esetén a továbbgyuruzo módszer a logikus. Ha ugyanis egy stúdió elnöke visszavonul, attól még a stúdió tovább muködik, egy darabig esetleg elnök nélkül. Ha azonban egy stúdió elnökének megváltozik az azonosítója, az valószínuleg csak valamilyen adminisztratív változást jelent. Valószínuleg az illeto továbbra is elnöke marad a stúdiónak, és ezért ezt a változást abban a relációban is követni kellene. D
7.1.3. Megszorítások
ellenörzésének
késleltetése
Vegyük a 7.1. példában említett' szituációt, ahol a Stúdióreláció elnökAzon tábla azonosító oszlopáattribútuma idegen kulcs, amely a GyártásIrányító ra hivatkozik. Tegyük fel, hogy Arnold Schwarzenegger Kalifornia kormányzójaként visszavonul, és elhatározza, hogy egy filmstúdiót alapít, amelynek neve La Vista Studios lesz. A stúdió elnöke természetesen o lesz. Ha végrehajtjuk a következo beszúrást, akkor gondban leszünk.
335 334
7. Megszorítások és triggerek
Lógó sorok
és módosítási
eljárások
Azokat a sorokat, amelyekben olyan idegen kulcs értéke szerepel, amely nincs benne a hivatkozott táblában, lógó soroknak nevezzük. Emlékezzünk rá, hogy azokat a sorokat is lógó soroknak neveztük, amelyek egy összekapcsolás esetén kimaradtak az eredménybol. A fenti két dolog szoros kapcsolatban áll egymással. Ha egy sorb eli idegen kulcs értéke hiányzik a hivatkozott táblából, akkor az adott sor nem lesz benne a relációnak a hivatkozott relációval vett összekapcsolásában. Ha az összekapcsolás az idegen kulcs, illetve a hivatkozott kulcs egyenloségén alapul, akkor az összekapcsolást idegenkulcs-összekapcsolásnak nevezzük. A lógó sorok pontosan azok a sorok lesznek, amelyek megsértik az idegenkulcs-megszorítás hivatkozásiépség-elvárását.
7.1. Kulcsok és idegen kulcsok 7.3. példa. Ha a gyártásirányítók csak stúdióelnökök lehetnének, akkor az azonosítóoszlopot idegen kulcsként kellene deklarálnunk, ami a Stúdió (elnökAzon) -ra hivatkozik. Ez esetben az elnökAzon oszlopot UNIQUEnak kellene deklarálnunk, aminek akkor van értelme, ha feltesszük, hogy egy ember nem lehet egyidejuleg két stúdiónak az elnöke. Ebben az esetben lehetetlen új stúdiókat és elnököket felvinni. Nem tudunk új elnökAzon értékkel rendelkezo sort beszúrni a Stúdió táblába, mert ez a sor megsértené azt az idegen kulcsot, ami az elnökAzon-ról a GyártásIrányító(azonosító)-ra mutat. Új azonosító értékkel rendelkezo sort sem tudunk beszúrni a GyártásIrányító táblába, mert az pedig azt az idegen kulcsot sérti meg, amely az azonosító-ról a Stúdió (elnökAzon)-ra mutat. O A 7.3. példában szereplo probléma másképp is megoldható: 1. Eloször is szükségünk van arra, hogy a két beszúrást (az egyiket a Stúdió, a másikat a GyártásIrányító táblába) egyetlen tranzakcióba foglaljunk.
INSERT INTO Stúdió VALUES('La
Vista',
'New York', 23456);
Az lesz a probléma, hogy nincs a GyártásIrányító táblának 23456 azonosítójú sora (feltételezzük, hogy ez lesz Arnold Schwarzenegger új azonosítója), és így nyilvánvaló módon megsért jük az idegenkulcs-megszorítást. Az egyik lehetséges megoldás, hogy eloször szúrjuk be a La Vista stúdióra vonatkozó sort az elnök azonosítója nélkül: INSERT INTO Stúdió(név, VALUES('La
Vista',
cím) 'New York');
Ez a módosítás már nem sérti meg a megszorítást, mivel a La Vistára vonatkozó sorban NULLérték kerül az elnökAzon oszlopba, és ez nem követeli meg semmilyen érték meglétét a hivatkozott oszlopban. Mielott azonban módosítani tudnánk az értéket az alábbi utasítással, eloször be kell szúrnunk egy Arnold Schwarzeneggerre vonatkozó sort a GyártásIrányító táblába a megfelelo azonosítóval. UPDATE
Stúdió
SET elnökAzon WHERE név
=
= 'La
23456 Vista';
Ha nem szúrjuk be elozoleg a megfelelo sort a GyártásIrányító táblába, akkor a fenti módosítás is meg fogja sérteni az idegenkulcs-megszorítást. A jelen példában, ha eloször beszúrjuk Arnold Schwarzeneggert és az o azonosítóját a GyártásIrányító táblába, mielott a La Vistát is beszúrnánk, ezzel biztosan elkerülhetjük az idegen kulcs megsértését. Vannak azonban olyan esetek, amikor a körkörös megszorítások miatt még az adatbázis-módosítások sorrendjének megfontolt megválasztásával sem tudjuk orvosolni a problémát.
2. Másrészt valahogy meg kell mondanunk az ABKR-nek, hogy a megszorításokat ne ellenorizze, amíg az egész tranzakció be nem fejezodött. A tranzakciók nyelvén ezt a befejezodést jóváhagyásnak nevezzük. Ahhoz hogy a 2. pontban az ABKR-t informáljuk, bármelyik megszorítás (kulcs, idegen kulcs vagy a fejezetben késobb eloforduló megszorítástípusok) deklarációját egyDEFERRABLE(késleltetheto) vagy egyNOT DEFERRABLEkulcsszó kell kövesse. Az utóbbi az alapértelmezés, és ez azt jelenti, hogy minden adatbázis-módosításkor a megszorítás közvetlenül utána ellenorzésre kerül, ha a módosítás egyáltalán megsértheti az idegenkulcs-megszorítást. Ha azonban a megszorítást DEFERRABLE-ként deklaráljuk, akkor lehetoségünk van arra, hogy azt mondjuk a rendszernek, hogy a megszorítás ellenorzésével várjon a tranzakció végéig.
A
DEFERRABLE
kulcsszót
követhet
egy
INITIALL Y DEFERRED vagy
egy
INITIALLYIMMEDIATE kiegészítés is. Az elso esetben az ellenorzés a tranzakció jóváhagyásáig késleltetve lesz. A második esetben az ellenorzés minden egyes utasítás után azonnal megtörténik. 7.4. példa. A 7.2. ábra a Stúdióreláció módosított deklarációját mutatja. A módosítás az idegen kulcs késleltetését teszi lehetové a tranzakció végéig. Az elnökAzon oszlopot UNIQUE-nakdeklaráltuk, hogy más relációk idegenkulcsmegszorításai hivatkozhassanak rá. Ha a 7.3. példában szereplo másik idegenkulcs-deklarációt is megadtuk volna a Gyártáslrányító(azonosító)-ról a Stúdió(elnökAzon)-ra, akkor olyan tranzakciókat írhatnánk, amelyek két sort szúrnak be (mindegyik táblába), és a két idegen kulcs csak a két beszúrás után kerül ellenorzésre. Így ha egy új stúdiót és az új elnököt is beszúrjuk ugyanazzal az azonosítóval-;-akkor nem sérül meg egyik megszorítás sem. D
336 7. Megszorítások és triggerek CREATE TABLE Stúdió
(
név CHAR(30) PRIMARY cím VARCHAR(255) , elnökAzon
e) A feltétel legyen ugyanaz, mint a)-ban, de a megszorítást megsérto törlések vagy módosítások esetén törölje vagy módosítsa a rendszer a Filmek reláció megfelelo sorait is.
KEY,
INT UNIQUE
REFERENCES DEFERRABLE
);
d) A SzerepelBenne relációban lévo filmeknekbenne kell lenniük a Filmek
GyártásIrányító(azonosító) INITIALLY DEFERRED
táblában is. A megszorítást megsérto utasításokat vissza. e)
7.2. ábra.
Az
elnökAzon
egyedisége
és a megszorítás
késleltetése
csolatban: Két további dolgot kell megemlítenünk a megszorítások késleltetéséveI kap-
·
Bármelyikfajta megszorításnak nevet adhatunk. Hogy ezt hogyan tehetjük, arról majd a 7.3.1. alfejezetben szólunk bovebben.
·
Ha egy megszorításnak nevet adtunk (például saját), akkor egy késleltethet o megszorítást azonnaliról késleltetettre változtathatunk a következo SQL-utasítással:
Ennek a fordítottját is megtehetjük, ha a DEFERRED helyett az IMMEDIATE használjuk.
SzerepelBenne relációban levo színészeknek benne kell lenniük a FilmSzínész táblában is. A megszorítást megsérto utasításokat a rendszer kezelje le a megfelelo sorok törlésével.
! 7.1.2. feladat. Azt a megszorítást szeretnénk megadni, hogy minden filmnek, amely a Filmek relációban szerepel, legalább egy színésszel benne kell lennie a SzerepelBenne relációban is. Megadhatjuk-eezt egy idegen kulcs segítségével? Indokoljuk meg a választ! 7.1.3. feladat. Javasoljunk megfelelo kulcsokat és idegen kulcsokat a 2.4.1. feladat PC-adatbázisának relációihoz. Módosítsuk a 2.3.1. feladatban szereplo SQL-sémát úgy, hogy az tartalmazza ezeknek a kulcsoknak a deklarációját. cd, ár) merevlemez, képernyö,
merevlemez,
Laptop(modell, sebesség, memória, Nyomtató (modell , színes, típus, ár)
ár)
7.1.4. feladat. Javasoljunk megfelelo kulcsokat a 2.4.3. feladatban szereplo Csatahaj ók adatbázis relációihoz. Módosítsuk a 2.3.2. feladatban szereplo SQLsémát úgy, hogy az tartalmazza ezeknek a kulcsoknak a deklarációját.
7.1.4. Feladatok 7.1.1. feladat. A 2.2.8. alfejezetben bevezetett filmes adatbázisban minden relációhozadtunk meg kulcsot. Filmek(filmcím, év, hossz, mufaj, stúdióNév, SzerepelBenne(filmCím, filmÉv, színészNév) FilmSzínész(~, cím, nem, születésiDátum) GyártásIrányító(név, cím, azonosító, Stúdió(név, cím, elnökAzon)
a rendszer utasítsa
A
Termék (gyártó , modell, típus) PC (modell , sebesség, memória,
SET CONSTRAINT saját DEFERRED;
kulcsszót
337
7.1. Kulcsok és idegen kulcsok
producerAzon)
nettóBevétel)
Adjuk meg a következo hivatkozásiépség-megszorítások deklarációit a filmadatbázisra: aj Egy film producerének szerepelnie kell a gyártásirányítók között. A GyártásIrányító tábla olyan módosításait, amelyek ezt a megszorítást megsértenék, utasítsa vissza a rendszer. bJ A feltétel legyen ugyanaz, mint az elobb, de a megszorítást megsért o módosítások NULL-ra. esetén a Filmekreláció producerAzon oszlopát változtassa a rendszer
Haj óosztályok (hajóosztály , típus, ország, kaliber, vízkiszorítás) Hajók(név, hajóosztály, Csaták (név , dátum) Kimenetelek(hajó,
ágyúkSzáma,
felavatva)
csata, eredmény)
7.1.5. feladat. Adjuk meg a következo hivatkozásiépség-megszorításokat az elozo feladatban szereplo adatbázissémára vonatkozóan. Az ott általunk megadott kulcsokat használjuk, és a megszorításokat megsérto utasításokat kezeljük le úgy, hogy a megfelelo értékeket NULL-raváltoztatjuk.
a) Minden Hajók táblabeli hajóosztálynak szerepelniekell a Hajóosztályok táblában is. b) Minden Kimenetelek táblabeli csatának szerepelnie kell a CsatáktáblábaB is. ej Minden Kimenetelek táblabeli hajónak szerepelnie kell a Hajóktáblában is.
