Adatbázis I. – 11. előadás Kulcsok az SQL – ben. Hivatkozásépségi megszorítások és idegen kulcsok. 1
1. Kulcsok az SQL-ben 2. Hivatkozási épség és idegen kulcsok 3. Attribútum értékre vonatk. megszorítások 4. Globális megszorítások 5. Megszorítások módosítása 6. Triggerek az SQL-ben 7. Tranzakciók
2
• •
•
•
0. Bevezetés Minden beszúrás, törlés és módosítás után ellenőrzést kellene végrehajtani. Az SQL 2 lehetőséget ad az adatbázis épségi megszorításainak az adatbázissémában való megadására. A triggerek aktív elemek amelyek bizonyos események hatására lépnek működésbe.. Csak az SQL 3 ban vannak benne 3
1. Kulcsok az SQL-ben Legfontosabb megszorítás a kulcsalkotás deklarációja: egy attribútum vagy attribútumhalamaz egy relációnak a kulcsa ( nem lehet két sornak megegyező értékű attribútuma vagy attribútumhalmaza a kulcs attribútumo(ko)n) Kulcsok megadása a CREATE TABLE utasításnál PRIMARY KEY es UNIQUE kulcsszavakkal történik. A kulcs megadása: 1.
Attribútum deklarálása relációsémában mint elsődleges kulcs
2.
Hozáadunk egy további deklarációt, ami azt mutatja meg hogy az attribútum vagy attribútumhalmaz kulcsot alkot 4
A kulcs megadása 2 féle képpen. CREATE TABLE szemely( CNP Bigint PRIMARY KEY, Név VARCHAR(50) ); CREATE TABLE szemely(CNP Bigint, Név VARCHAR(50), PRIMARY KEY(CNP) ); Ha több, mint 1 attribútum van, akkor csak így lehet megadni!!!!!!! 5
•
Feltételek (egy adott oszlopra vonatkoznak):
• • •
PRIMARY KEY: elsődleges kulcs UNIQUE: kulcs REFERENCES tábla(oszlop) [ON-feltételek]: külső kulcs
•
Táblafeltételek (az egész táblára vonatkoznak):
•
PRIMARY KEY (oszloplista): elsődleges kulcs
•
UNIQUE (oszloplista): kulcs
•
FOREIGN KEY (oszloplista) REFERENCES tábla(oszloplista) [ON-feltételek]: külső kulcs 6
Zölddel írva a számla általános adatait láthatjuk Pirossal a számlasorok attribútumai vannak Kékkel írva láthatóak a számlasorok. 7
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Csíkszereda
számlaszám
ÁFA
határidő
sorai
Számlák Dátum
Sorszám Számlasorok egységár
számlája
azonosító
terméke Ügyfelek
név
darabszám
bank
termékID
név
bszámla Termékek
adószám
mértékegység
bejegyzés 8
1. Példa : a név atribútum mint elsődleges kulcs CREATE TABLE Szamla ( Szamlaszam CHAR(14) PRIMARY KEY, Datum DATE, AFA INT, KliensID INT NOT NULL REFERENCES Kliens(ID)); 2. Példa: elsődleges kulcs különálló deklarálása CREATE TABLE Szamlasor ( Szamlaszam CHAR(14), Sorszam INT, Mennyiség FLOAT, Egysegar FLOAT, Termekkod INT REFERENCES Termek(termekID), PRIMARY KEY(Szamlaszam,Sorszam) FOREIGN KEY (Szamlaszam) REFERENCES Szamla(Szamlaszam); 9
CREATE TABLE Konyv( KOD BIGINT PRIMARY KEY, Szerzo VARCHAR(45), Cim VARCHAR(55) ); CREATE TABLE Konyvpeldany( Leltarszam INT PRIMARY KEY, Vonalkod BIGINT UNIQUE, Konyvkod BIGINT REFERENCES Konyv(KOD) ); 10
vagy egy más lehetőség a UNIQUE
használata: Példa Számla: Szamlaszam CHAR(14) UNIQUE UNIQUE(Szamlaszam) Számlasorok esetében csak a második használható UNIQUE(Szamlaszam, Sorszam) 11
• • • • • • •
Kulcsfeltételek teljesítésének biztosítása: Lehetőség van az index deklarálásához, gyorsabb keresést biztosít CREATE UNIQUE INDEX nevIndex ON Kliens(Név) A kulcsfeltételt minden módosítas után ellenőrizni kell. Csak a beszúrás és modosítás okoz gondot, a törlés nem. Ha nincs index akkor a reláció minden sorát ellenőrizni kell. Ez nagyon időigényes művelet. 12
elsődleges kulcs és egyedi attribútumok hasonlatossága és különbsége: hasonlatosság PRIMARY KEY es UNIQUE szinonímák különbség 1. PRIMARY KEY egyszer fordul elő egy táblában amíg UNIQUE többször is előfordulhat 2. Idegen kulcs csak elsődleges kulcsra vonatkozhat 3. PRIMARY KEY hez indexet lehet hozzákapcsolni 13
2. Hivatkozási épség és idegen kulcsok Hivatkozási épség azt jelenti hogy bizonyos attribútumoknak megfelelő érteme KELL legyen. Példa: a Számla relációban a KliensID atribútum értéke 2563, akkor a Kliens relációban van egy sor amelyben az azonosito 2563. Idegen kulcsok más relációra vonatkoznak , más attribútumokra 1. A másik reláció attribútumai, amelyekre hivatkozunk elsődleges kulcsok 2. A Hivatkozási épség megszorítás a két attribútumot (attribútum-csoportot) összekapcsolja 14
Idegen kulcsokat kétféle képpen deklarálhatjuk: 1. Az idegen kulcs egy attribútumra vonatkozik REFERENCES
() 2. A CREATE TABLE utasítás végén deklaráljuk FOREIGN KEY REFERENCES () 15
1. Példa : idegen kulcs egy attribútumra vonatkozik CREATE TABLE Számla ( Számlaszám CHAR(14) PRIMARY KEY, Dátum DATE, ÁFA INT, KliensID INT NOT NULL REFERENCES Kliens(ID)); 2. Példa: mindkét eset előfordul egy tábla készítésénél CREATE TABLE Számlasor ( Számlaszám CHAR(14), Sorszám INT, Mennyiség FLOAT, Egységár FLOAT, Termékkód INT REFERENCES Termek(termekID), PRIMARY KEY(Számlaszám,Sorszám) FOREIGN KEY (Számlaszám) REFERENCES Számla(Számlaszám)); 16
Hivatkozási épség fenntartása Futási hiba: 1. Számla relációba beszúrunk egy olyan sort, amelyben a KliensID nem egyezik a Kliens táblából egyik klienskóddal sem 2. Számla reláció egy sorában megpróbáljuk módosítani a KliensID-t, olyanra ami nem létezik a Kliens táblában 3. Kliens táblából törölni próbálunk egy olyan sort, amelyikben levő klienskód létezik valamelyik sorban a Számla relációban 4. Kliens relációban módosítása olyan klienskód értékének, amelyik benne van a Számla reláció legalább egy sorában Az adatbaziskezelőnek három lehetősége van: •. Alapértelmezés szerinti eljárás . Ha a feltétel megsérül a módosítást visszautasítjuk. 17
Továbbgyűrűző eljárás Számlaszám BX 2556345 BX2556346
Dátum 2006.05.01 2006.05.01
ÁFA 19 19
KliensID 2584 2563
BX2556347 BX2556348
2006.05.02 2006.05.03
19 19
2563 2584
Klienskód
Kliensnév
Bank
2563 2584
MarcoSoft GigaByte
BCR Reiffeisen
.....
18
ON UPDATE CASCADE Számlaszám BX 2556345 BX2556346
Dátum 2006.05.01 2006.05.01
ÁFA 19 19
KliensID 2584 2888
BX2556347 BX2556348
2006.05.02 2006.05.03
19 19
2888 2584
Klienskód 2888
Kliensnév MarcoSoft
Bank BCR
2584
GigaByte
Reiffeisen
.....
19
ON DELETE SET NULL Törölve volt a 2888-as Kliens, a MacroSoft Számlaszám BX 2556345 BX2556346
Dátum 2006.05.01 2006.05.01
ÁFA 19 19
KliensID 2584 NULL
BX2556347 BX2556348
2006.05.02 2006.05.03
19 19
NULL 2584
Klienskód
Kliensnév
Bank
2584
GigaByte
Reiffeisen
.....
20
A tábla készítése, hogy az előbbi módon működjék CREATE TABLE Számla( Számlaszám CHAR(14) PRIMARY KEY, Dátum DATE, határidő DATE, ÁFA INT, Fizetve BOOLEAN, KliensID INT REFERENCES Kliens(Klienskód), ON DELETE SET NULL, ON UPDATE CASCADE ); 21
Zölddel írva a számla általános adatait láthatjuk Pirossal a számlasorok attribútumai vannak Kékkel írva láthatóak a számlasorok. 22
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Csíkszereda
számlaszám
ÁFA
határidő
sorai
Számlák Dátum
Sorszám Számlasorok egységár
számlája
azonosító
terméke Ügyfelek
név
darabszám
bank
termékID
név
bszámla Termékek
adószám
mértékegység
bejegyzés 23
CREATE TABLE Számla(... KliensID INT REFERENCES Kliens(Klienskód) ON DELETE CASCADE ON UPDATE CASCADE ); Ha kitörölünk egy KLIENSt a kliens táblából, akkor az összes hozzá tartozó számla is kitörlődik. Ez sem jó, de az adatbázisunk bizonyos szempontból konszisztens maradt, viszont hiányoznak bizonyos számlák.
24
CREATE TABLE Számlasor(... Termékkód INT REFERENCES Termek(termekID) ON DELETE CASCADE, FOREIGN KEY (Számlaszám) REFERENCES Számla(Számlaszám) ON DELETE CASCADE ON UPDATE CASCADE ); 1. Az első esetben kitörlődik az összes olyan számlasor, amelyikben egy bizonyos termék megtalálható. Ez teljesen inkonszisztens, mert lehet olyan számlánk, amelyikből csak bizonyos sorok maradtak meg. A számla összértéke is megválozik a törlés miatt. 2. A második eset valamivel jobb, sőt, konszisztencia őrzője, mivel, ha kitörölünk egy számlát, akkor törlődik 25 vele együtt az összes hozzá tartozó számlasor is.
3. A törlés szerinti CASCADE (továbbgyűrűzés) érdekesen alakulhat az előzőek szerint: Ha kitörlünk egy ügyfelet, akkor azzal együtt kitörlődnek a hozzá tartozó számlák. Mivel a számlasorok a számlákhoz ugyancsak CASCADE szerint vannak kapcsolva, kitörlődnek az összes hozzátartozó számlasorok is. Ez a példa mutatja be az igazi továbbgyűrzést, mert egy táblában való törlés, másik két táblában való törlést eredményez.
26
Lógó sorok es módosító eljárások: Definíció: Lógó sorok azok a sorok amelyek kulcsértéke nincs benne a hivatkozott táblában. Megsértik az adott idegen kulcs megszorítására vonatkozó hivatkozási épséget. Az alapértelmezett eljárás a törlés és módosításokra: Tiltottak azon műveletek, amelyek lógó sorokat hoznak létre abban a relációban, amelyik a másikra hivatkozik. Továbbgyűrűző eljárás: Törli vagy módosítja minden létrejött lógó sort NULL értékre állítás: Az idegen kulcsot NULL értekre változtatja az összes lógó sorban 27
Abban az esetben ha nem adtuk meg ELŐRE a HIVATKOZÁSÉPSÉGI megszorítást, megtörténhet, hogy lógó soraink maradnak, s ezekután nem tudjuk megadni a hivatozás-épségi megszorítást. A lógó sorokat a LEFT JOIN és a RIGHT JOIN összekapcsolás is megmutatja. A LEFT JOIN esetében a hivatkozó tábla a FROM után az első kell legyen: SELECT Számla.Számlaszám, Kliens.nev FROM Számla LEFT JOIN Kliens ON Számla. KliensID=Kliens.ID; Megadja az összes számlát és a hozzátartozó kliens nevét. (ha van) 28
Azon számlák megkeresése, amelyek “lógnak” a következő képpen is lehetségesek: SELECT Számla.Számlaszám FROM Számla WHERE Számla.KliensID NOT IN (SELECT Kliens.ID FROM Kliens);
29
3. Attribútum értékre vonatkozó megszorítások Kétféle képpen adható meg: 1. Attribútumokra vonatkozó megszorítás a relacióséma definálásakor adható meg. 2. Értéktartományra vonatkozó megszorítás, majd az attribútum hozzárendelése az értéktartományhoz. •. •.
Attribútumokra vonatkozó CHECK feltételek korlátozzák az attribútumértéket. Példa: CHECK (elnökazon >=100000), CHECK (nem In (‘F’, ‘N’) 30
A NOT NULL feltételek Példa (a Számla relációban) : KliensID INT REFERENCES Kliens (ID) NOT NULL Következmények: Nem módosíthatjuk a kliensID attribútumot NULL ra Nem szúrhatunk egy sort be a Számla relációba amelyre csak számlaszámot, dátumot, határidőt és ÁFA – t írunk mert akkor a KliensID NULL lenne Nem alkalmazhatjuk a NULL értékre állítás módszerét 31
Értéktartományra vonatkozó megszorítások: Értéktartomány megadásával az attribútum értékeit korlátozhatjuk. Az értéktartományra való hivatkozás a VALUE kulcsszóval történik CREATE DOMAIN NemÉrték CHAR (1) CHECK (VALUE IN (‘F’,’N’)); Mikor kell egy megszorítást ellenőrizni? TRANZAKCIÓ után, ha DEFERRED – ként deklaráljuk. Létezhet olyan kapcsolat, amelyik nem engedi a bevitelt, csak ha 2 helyre is bevisszük egyszerre. 32
4. Globális megszorítások Több attribútumra vonatkozó megszorítások. A problémát két részre osztjuk: 1. Sorra vonatkozó CHECK fetétel 2. Önálló megszorítások Sorra vonatkozó CHECK feltétel: CREATE TABLE utasításban adjuk meg az attribútumok, kulcsok és idegen kulcsok deklarációja után. Bármi lehet, ami a WHERE után szerepel. A rendszer akkor ellenőrzi, amikor egy új sort szúrunk be R – be, vagy amikor R egy sorát módosítjuk
33
Önálló megszorítások -Reláció egészére : pl. oszlopbeli értékek összegére ad feltételt 1. CREATE ASSERTION kulcsszó, 2. Az önálló megszorítás neve, 3. A CHECK kulcsszó 4. Egy zárójelben megadott feltétel CREATE ASSERTION CHECK ()
34
Sapientia - Erdélyi Magyar TudományEgyetem (EMTE) Csíkszereda Megszorítások összehasonlítása: Megszorítas Tipus
Hol deklaráljuk
Mikor ellenőrzi a rendszer
Minden esetben igaz marad?
Attr. Vonatkozó CHECK feltétel
Az attribútum megadásakor
A relációba való beszúráskor vagy az attrib. módosít
Alkérdés esetén nem
Sorra vonatkozó CHECK feltétel
A relációséma megadásakor
A rel.való beszúr. vagy sor módosít.
Alkérdés esetén nem
Önálló megszorítás
Az adatbázisséma megadásakor
Bármelyik,a feltételben szereplő rel. Változtat.
Igen
35
5. Megszorítások módosítása Megszorításokat módosíthatjuk, törölhetjük vagy helyettesíthetjük: Megszorítások elnevezése A törléshez szükség van arra, hogy a Megszorításoknak neve legyen. A CREATE ASSERTION és CONSTRAINT kulcsszavakkal történik Példa: Nev CHaR (30) CONSTRAINT Nevkulcs PRIMARY KEY, Nem CHAR (1) CONSTRAINT ferfivagyno CHECK (nem IN (‘F’, ‘N’)); 36
Tablákra vonatkozó megszorítások megváltoztatása: ALTER TABLE kulcsszavakkal, DROP-al törölünk ADD-all hozzáteszünk egy új megszorítást Példa: ALTER TABLE Filmszinesz DROP CONSTRAIN nevkulcs; ALTER TABLE Filmszinesz ADD CONSTRAIN nevkulcs PRIMARY KEY (nev); Értéktartományra vonatkozó megszorítások megváltoztatása Példa: ALTER DOMAIN Azonertek DROP CONSTRAIN Hatjegyu ALTER DOMAIN Azonertek ADD CONSTRAIN Hatjegyu CHECK (VALUE<=100000); 37
Önálló megszorítások megváltoztatása: A DROP ASSERTION kulcsszavakkal törölünk Létrehozás új deklarációval végezzük el Példa : DROP ASSERTION gazdagelnok
38
