Megszortsok Idegen kulcsok Loklis s globlis megszortsok Triggerek

Megszorítások Idegen kulcsok Lokális és globális megszorítások Triggerek 1

Megszorítások és triggerek u. A megszorítás adatelemek közötti kapcsolat, amelyet az AB rendszernek fent kell tartania. w Példa: kulcs megszorítások. u. Triggerek olyankor hajtódnak végre, amikor valamilyen megadott esemény történik, mint pl. sorok beszúrása egy táblába. 2

Megszorítások típusai u. Kulcsok. u. Idegen kulcsok, vagy hivatkozási épség megszorítás. uÉrték-alapú megszorítás. w Egy adott attribútum lehetséges értékeiről mond valamit. u. Sor-alapú megszorítás. w Mezők közötti kapcsolatok leírása. u. Globális megszorítás: bármilyen SQL kifejezés. 3

Emlékeztető: egy attribútumos kulcsok u. PRIMARY KEY vagy UNIQUE. u. Példa: CREATE TABLE Sörök ( név CHAR(20) UNIQUE, gyártó CHAR(20) ); 4

Emlékeztető: kulcsok több attribútummal CREATE TABLE Felszolgál ( kocsma CHAR(20), sör VARCHAR(20), ár REAL, PRIMARY KEY (kocsma, sör) ); 5

Idegen kulcsok u. Egy reláció attribútumainak értékei egy másik reláció értékei között is meg kell, hogy jelenjenek együttesen. u. Példa: a Felszolgál(kocsma, sör, ár) táblánál azt várnánk, hogy az itteni sörök szerepelnek a Sörök tábla név oszlopában is. 6

Idegen kulcsok megadása u A REFERENCES kulcsszót kell használni: 1. egy attribútum után (egy-attribútumos kulcs) 2. A séma elemeként: FOREIGN KEY (<attribútumok listája>) REFERENCES <reláció> (<attribútumok>) u A hivatkozott attribútum(ok)nak kulcsnak kell lennie / lenniük (PRIMARY KEY vagy UNIQUE). 7

Példa: egy attribútum CREATE TABLE Sörök ( név CHAR(20) PRIMARY KEY, gyártó CHAR(20) ); CREATE TABLE Felszolgál ( kocsma CHAR(20), sör CHAR(20) REFERENCES Sörök(név), ár REAL ); 8

Példa: a séma elemeként CREATE TABLE Sörök ( név CHAR(20) PRIMARY KEY, gyártó CHAR(20) ); CREATE TABLE Felszolgál ( kocsma CHAR(20), sör CHAR(20), ár REAL, FOREIGN KEY(sör) REFERENCES Sörök(név)); 9

Idegen kulcs megszorítások megőrzése u Egy idegen kulcs megszorítás R relációról S relációra kétféleképpen sérülhet: 1. Egy R-be történő beszúrásnál S-ben nem szereplő értéket adunk meg. 2. Egy S-beli törlés „lógó” sorokat eredményez R-ben. 10

Hogyan védekezzünk? --- (1) u. Példa: R = Felszolgál, S = Sörök. u. Nem engedjük, hogy Felszolgál táblába a Sörök táblában nem szereplő sört szúrjanak be. u. A Sörök táblából való törlés, ami a Felszolgál tábla sorait is érintheti (mert sérül az idegen kulcs megszorítás) 3 -féle módon kezelhető. 11

Hogyan védekezzünk? --- (2) 1. Default : a rendszer nem hajtja végre a törlést. 2. Továbbgyűrűzés : a Felszolgál tábla értékeit igazítjuk a változáshoz. w Sör törlése: töröljük a Felszolgál tábla megfelelő sorait. w Sör módosítása: a Felszolgál táblában is változik az érték. 3. Set NULL : a sör értékét állítsuk NULL-ra az érintett sorokban. 12

Példa: továbbgyűrűzés u. Töröljük a Bud sort a Sörök táblából: w az összes sort töröljük a Felszolgál táblából, ahol sör oszlop értéke ’Bud’. u. A ’Bud’ nevet ’Budweiser’-re változtatjuk: w a Felszolgál tábla soraiban is végrehajtjuk ugyanezt a változtatást. 13

Példa: Set NULL u. A Bud sort töröljük a Sörök táblából: w a Felszolgál tábla sör = ’Bud’ soraiban a Budot cseréljük NULL-ra. u’Bud’-ról ’Budweiser’-re módosítunk: w ugyanazt kell tennünk, mint törléskor. 14

A stratégia beállítása u. Ha egy idegen kulcsot deklarálunk megadhatjuk a SET NULL és a CASCADE stratégiát is beszúrásra és törlésre is egyaránt. u. Az idegen kulcs deklarálása után ezt kell írnunk: ON [UPDATE, DELETE][SET NULL CASCADE] u. Ha ezt nem adjuk meg, a default stratégia működik. 15

Példa: stratégia beállítása CREATE TABLE Felszolgál ( kocsma CHAR(20), sör CHAR(20), ár REAL, FOREIGN KEY(sör) REFERENCES Sörök(név) ON DELETE SET NULL ON UPDATE CASCADE ); 16

Attribútum alapú ellenőrzések u. Egy adott oszlop értékeire vonatkozóan adhatunk megszorításokat. u. Adjuk hozzá a CHECK(<condition>) utasítást az attribútum deklarációjához. u. A feltételben csak az adott attribútum neve szerepelhet, más attribútumok (más relációk attribútumai is) csak alkérdésben szerepelhetnek. 17

Példa: attribútum alapú ellenzőrzés CREATE TABLE Felszolgál ( kocsma CHAR(20), sör CHAR(20) CHECK ( sör IN (SELECT name FROM Sörök)), ár REAL CHECK ( ár <= 5. 00 ) ); 18

Mikor ellenőriz? u. Attribútum-alapú ellenőrzést csak beszúrásnál és módosításnál hajt végre a rendszer. w Példa: CHECK (ár <= 5. 00) a beszúrt vagy módosított sor értéke nagyobb 5, a rendszer nem hajtja végre az utasítást. w Példa: CHECK (sör IN (SELECT név FROM Sörök), ha a Sörök táblából törlünk, ezt a feltételt nem ellenőrzi a rendszer. 19

Oszlop-alapú megszorítások u. A CHECK (<feltétel>) megszorítás a séma elemeként is megadható. u. A feltételben tetszőleges oszlop és reláció szerepelhet. w De más relációk attribútumai csak alkérdésben jelenhetnek meg. u. Csak beszúrásnál és módosításnál ellenőrzi a rendszer. 20

Példa: oszlop-alapú megszorítások u. Csak Joe bárjában lehetnek drágábbak a sörök 5 dollárnál: CREATE TABLE Felszolgál ( kocsma CHAR(20), sör CHAR(20), ár REAL, CHECK (kocsma = ’Joe bárja’ OR ár <= 5. 00) ); 21

Globális megszorítás u. Ezek az adatbázissémához tartoznak a relációsémákhoz és nézetekhez hasonlóan. u CREATE ASSERTION <név> CHECK (<feltétel>); u. A feltétel tetszőleges táblára és oszlopra hivatkozhat az adatbázissémából. 22

Példa: globális megszorítás CREATE ASSERTION Csak. Olcsó CHECK ( NOT EXISTS ( SELECT kocsma FROM Felszolgál Kocsmák, ahol a sörök GROUP BY kocsma átlagosan drágábbak 5 HAVING 5. 00 < AVG(ár) dollárnál. )); 23

Példa: globális megszorítás u. Az Alkesz(név, cím, telefon) és Kocsma(név, cím, engedély. Szám), táblákban nem lehet több kocsma, mint alkesz. CREATE ASSERTION Több. Alkesz CHECK ( (SELECT COUNT(*) FROM Kocsma) <= (SELECT COUNT(*) FROM Alkesz) ); 24

Globális megszorítások ellenőrzése u. Alapvetően az adatbázis bármely módosítása előtt ellenőrizni kell. u. Egy okos rendszer felismeri, hogy mely változtatások, mely megszorításokat érinthetnek. w Példa: a Sörök tábla változásai nincsenek hatással az iménti Több. Alkesz megszorításra. Ugyanez igaz az Alkesz táblába történő beszúrásokra is. 25

Miért hasznosak a triggerek? u. A globális megszorításokkal sok mindent le tudunk írni, az ellenőrzésük azonban gondot jelenthet. u. Az attribútum- és oszlop-alapú megszorítások ellenőrzése egyszerűbb (tudjuk mikor történik), ám ezekkel nem tudunk mindent kifejezni. u. A triggerek esetén a felhasználó mondja meg, hogy egy megszorítás mikor 26 kerüljön ellenőrzésre.

Esemény-Feltétel-Akció szabályok u. A triggereket esetenként ECA szabályoknak (event-condition-action) is nevezik. u. Esemény: általában valamilyen módosítás a adatbázisban, például egy sor beszúrása. u. Feltétel : bármilyen SQL igaz-hamis- (ismeretlen) feltétel. u. Akció : bármilyen SQL utasítás. 27

Példa: trigger u. Ahelyett, hogy visszautasítanánk a Felszolgál(kocsma, sör, ár) táblába történő beszúrást az ismeretlen sörök esetén, a Sörök(név, gyártó) táblába is beszúrjuk a megfelelő sort a gyártónak NULL értéket adva. 28

Példa: trigger definíció Az esemény CREATE TRIGGER Sör. Trig BEFORE INSERT ON Felszolgál REFERENCING NEW ROW AS Új. Sor FOR EACH ROW A feltétel WHEN (Új. Sor. sör NOT IN (SELECT név FROM Sörök)) INSERT INTO Sörök(név) Az akció VALUES(Új. Sor. sör); 29

Opciók: CREATE TRIGGER u. CREATE TRIGGER <név> u. Vagy: CREATE OR REPLACE TRIGGER <név> w Akkor használható, ha már létezik egy ilyen nevű trigger és azt módosítani szeretnénk. 30

Opciók: Az esemény u. AFTER, BEFORE. w Emellett, INSTEAD OF, ha nézettábláról van szó. • Nézetekbe sok esetben triggerek segítségével szúrunk be. u. INSERT, DELETE vagy UPDATE. w Az UPDATE lehet UPDATE. . . ON és egy attribútum. 31

Opció: FOR EACH ROW u. Triggerek vagy “sorszintűek” vagy “utasításszintűek. ” u. A FOR EACH ROW jelenléte sorszintű végrehajtásra ad utasít; különben utasításszintű a trigger. u. Sorszintű triggerek : minden módosított sorra végrehajtódik egyszer. u. Utasításszintű triggerek : egyszer hajtódik végre az SQL utasításra függetlenül az érintett sorok számától. 32

Opciók: REFERENCING u. Az INSERT utasítás sorszinten egy új sort feltételez, utasításszinten pedig egy új táblát. w Itt a “tábla” a beszúrt sorok (multi)halmaza. u. A DELETE egy törölt sort vagy törölt sorok halmazát feltételezi. u. Az UPDATE mindkettőt. u. Ezekre hivatkozás: [NEW OLD][TUPLE TABLE] AS <név> 33

Opciók: a feltétel u. Tetszőleges logikai értéket visszaadó feltétel. u. AFTER vagy BEFORE triggertől függően az utasítás előtti vagy után adatbázison értékelődik ki. w De mindig azelőtt, mielőtt a módosítások ténylegesen lezajlanának. u. Az új/régi sort/táblát a REFERENCING záradékban megadott néven érhetjük el. 34

Opciók: Az akció u. Több SQL utasítást is végrehajthatunk itt. w Ezeket BEGIN. . . END utasítások közé kell tennünk. uÁltalában a módosító utasítások játsszák a főszerepet. 35

Egy másik példa u. A Felszolgál(kocsma, sör, ár) segítségével elkészítjük a Drága. Kocsmák(kocsma) táblát, ahol azok a kocsmák szerepelnek, melyek legalább egy sörük árát egy dollárral emelték. 36

The Trigger Az esemény – Felszolgál ár oszlopa változik. CREATE TRIGGER Ár. Trig AFTER UPDATE OF ár ON Felszolgál REFERENCING A módosítás során az új és régi sort is Feltétel: OLD ROW AS ooo használhatjuk. az áremel. NEW ROW AS nnn Minden megváltozott kedés > $1 sorra… FOR EACH ROW WHEN(nnn. price > ooo. price + 1. 00) INSERT INTO Drága. Kocsmák Ha az áremelkedés elég magas, a kocsma VALUES(nnn. bar); neve bekerül a táblába. 37

Megszorítások ellenőrzése --1 u. Az Oracle (11 g Release 2. 1) a következőképpen ellenőrzi a megszorításokat triggerek esetén. u. Run all BEFORE statement triggers that apply to the statement. 38

Megszorítások ellenőrzése --2 u. Loop for each row affected by the SQL statement. w Run all BEFORE row triggers that apply to the statement. w Lock and change row, and perform integrity constraint checking. (The lock is not released until the transaction is committed. ) w Run all AFTER row triggers that apply to the statement. 39

Megszorítások ellenőrzése --3 u. Complete deferred integrity constraint checking. u. Run all AFTER statement triggers that apply to the statement. 40