por Alexandra Paál hace 5 años
384
Ver más
A PRIMARY KEY VAGY UNIQUE KEY megszorítás nem dobható el, ha FOREIGN KEY megszorításban hivatkozunk rá.
Az adatbázis kényszereket más néven (deklaratív) adatintegritási szabályoknak nevezik (röviden angolul constraint). Az adatbázisok tartalmát lehet velük jellemezni és korlátozni, ezzel elősegítve a konzisztencia fenntartását. Deklaratív jellegük miatt a DDL nyelvhez tartoznak szintaktikailag, egy táblára, vagy annak oszlopaira vonatkoznak.
Egy példa kényszerre: két tábla esetén, melyek között szülő-gyerek kapcsolat áll fenn, a gyerek tábla ID oszlopa csak a szülő táblából vehet fel adatot, kézzel nem lehet bevinni
Források:
Az SQL szabvány nem definiálja elég szigorúan, ezért az egyes adatbáziskezelő rendszerek valamelyest eltérhetnek mind lehetőségekben, mind szintaxisban.
A kényszerek csökkentik a hibás adatbevitel lehetőségét, hibásra módosítását.
Azonban ezen öt kényszer általában létezik:
Megadhatók inline és out-of-line módon is. Az inline-t általában akkor használjuk, ha egy oszlopra vonatkozik a megszorítás, míg az out-of-line-t akkor, ha táblaszintű a kényszer, vagyis több oszlopra vonatkozik.
Az idegen kulcs kényszer azt gátolja meg, hogy olyan értéket vigyünk be egy tábla oszlopába, amely nem található meg egy táblánk elsődleges, vagy egyedi kulcsában. A hivatkozott táblát általában szülő, a hivatkozó táblát gyerek táblának szokták hívni.
Ha az idegen kulcs nem egy attribútumos, akkor csak "FOREIGN KEY oszlopnevek REFERENCES tábla (oszlopnevek)" módon adható meg.
Ha egy tábla FORIGN KEY kulcsa ugyanazaon tábla PRIMARY KEY kulcsára hivatkozik, szülő-gyerek kapcsolat jön létre, és a tábla egyik sorának nem lesz szülője. Így ebben a sorbana FOREIGN KEY oszlop értéke NULL Lesz.
Szintaxisa (inline):
CREATE TABLE egyiktábla(
oszlopnév1 adattípus
, oszlopnév2 adattípus REFERENCES másiktábla (másikoszlop)
, ....
);
Szintaxis (out-of-line):
CREATE TABLE egyiktábla(
oszlopnév1 adattípus
, oszlopnév2 adattípus
, ....
, FOREIGN KEY (oszlopnév1) REFERENCES másiktábla (másikoszlop)
);
Példa (out-of-line):
CREATE TABLE alkalmazott(
aid NUMBER(3)
, nev VARCHAR2(40)
,szulev NUMBER(4)
,FOREIGN KEY (aid) REFERENCES beosztas (bid)
);
RESTRICT
A RESTRICT alapbeállítás pedig nem enged szülőbejegyzést törölni, amíg van rá hivatkozó gyerektáblabeli rekord.
ON DELETE SET NULL
Másik lehetőség a ON DELETE SET NULL, amelynek hatására a szülő táblában történő törlés esetén a gyermek tábla megfelelő bejegyzései NULL értékre kerülnek beállításra.
ON DELETE CASCADE
Amennyiben a kényszerhez a ON DELETE CASCADE-et beírjuk, akkor törlési láncot hozunk létre. Ilyenkor ha egy rekordot kitörlünk a szülő táblában, a rendszer automatikusan törli a gyermek táblában a hozzá kapcsolódó sorokat.
A tábla azon kulcsa, mely nem az elsődleges. A PRIMARY KEY kényszerrel ellentétben UNIQUE KEY-ből bármennyi lehet (akár nulla is). Implementációs részlet, hogy a UNIQUE kulcsok oszlopainak lehet-e NULL értéke, mivel a NULL ismeretlen, ezért ezért nem szegjük meg vele az egyediséget. Továbbá a rendszer automatikusan létrehoz egy indexet a megadott mezők alapján ezzel gyorsítva a kereséseket.
Szintaxisa (inline):
CREATE TABLE táblanév(
oszlopnév1 adattípus
, oszlopnév2 adattípus CONSTRAINT kényszernév UNIQUE
, .......
);
Szintaxisa (out-of-line):
CREATE TABLE táblanév(
oszlopnév1 adattípus
, oszlopnév2 adattípus
, .......
, CONSTRAINT kényszernév UNIQUE(oszlopnév1, oszlopnév2)
);
Példa (out-of-line)
CREATE TABLE alkalmazott(
aid NUMBER(3) PRIMARY KEY
,nev VARCHAR2(40)
,szulev NUMBER(4)
,CONSTRAINT key_nev_szulev UNIQUE (nev,szulev)
);
A relációs adatmodellben eleméleti szinten minden kulcs egyenrangú, azonban a gyakorlatban célszerű, ha van egy elsődleges kulcs, melyből maximum csak EGY lehet táblánként. Ez biztosítja azt, hogy ne fordulhasson elő két azonos sor egy táblán belül. (Az általunk használt Oracle esetén így a NULL érték nincs megengedve az elsődleges kulcs oszlopban)
Szinatxisa (inline):
CREATE TABLE táblanév(
oszlopnév1 adattípus CONSTRAINT kényszernév PRIMARY KEY
, oszlopnév2 adattípus
, ....
);
Szintaxisa (out-of-line):
CREATE TABLE táblanév(
oszlopnév1 adattípus
, oszlopnév2 adattípus
, CONSTRAINT kényszernév PRIMARY KEY (oszlopnév1,oszlopnév2)
);
Példa (inline):
CREATE TABLE alkalmazott(
aid NUMBER(3) PRIMARY KEY
,nev VARCHAR2(40)
);
Az elsődleges kulcs állhat több oszlopból is, ekkor többoszloposnak, vagy konkatenáltnak, összetett kulcsnak nevezzük
Példa (out-of-line):
CREATE TABLE alkalmazott(
aid NUMBER(3)
,nev VARCHAR2(40)
,szulev NUMBER(4)
,CONSTRAINT key_nev_szulev PRIMARY KEY (nev,szulev)
);
Az elsődleges kulcs állhat egyetlen attribútumból, ekkor egyoszlopos kényszernek nevezzük
Példa (inline):
CREATE TABLE alkalmazott(
aid NUMBER(3) PRIMARY KEY
,nev VARCHAR2(40)
);
A tábla egy sorára vonatkozik, tehát minden sorra külön-külön igaznak kell lennie. A NOT NULL a CHECK egy speciális esetének tekinthető.
A CHECK-be egy logikai kifejezést adhatunk meg, hasonlóan mint a lekérdezések WHERE klauzulájában. A kifejezésben nem használhatunk bonyolultabb lekérdezéseket, melyek más táblákra is hivatkozhatnának. Nem szerepelhetnek továbbá nem determinisztikus visszatérési értékű függvények hívásai (pl. SYSDATE) sem a feltételben.
Szintaxisa (inline):
CREATE TABLE táblanév(
oszlopnév1 adattípus CONSTRAINT kényszernév1 CHECK (feltétel1)
, oszlopnév2 adattípus CONSTRAINT kényszernév2 CHECK (feltétel2)
, .....
);
Szintaxisa (out-of-line):
CREATE TABLE táblanév(
oszlopnév1 adattípus
, oszlopnév2 adattípus
, CONSTRAINT kényszernév CHECK (feltétel)
, .....
);
Példa (inline):
CREATE TABLE árfolyam(
datum DATE
, jpy NUMBER(5, 4) CONSTRAINT japan_yen CHECK(jpy BETWEEN 0 AND 5)
, usd NUMBER(5, 2) CONSTRAINT usa_dollar CHECK(usd BETWEEN 100 AND 400)
);
A tábla egy oszlopára vonatkozó szabály. Eredetileg nem is lehetett NULL érték, de később ezt megengedték az implementáció során, de ezen korlátozással ezt a problémát ki lehet küszöbölni.
Szintaxisa:
CREATE TABLE táblanév(
oszlopnév1 adattípus NOT NULL
, oszlopnév2 adattípus NOT NULL
, .....
);
Példa:
CREATE TABLE alkalmazott(
aid NUMBER(3)
,nev VARCHAR2(40) NOT NULL
,szulev NUMBER(4) NOT NULL
);
Sokszor hasznos lenne, ha az öt kényszertípus mellett bonyolultabb szabályokat is lehetne alkalmazni. Ha a deklaratív szabályok ez nem lehetséges, akkor programkóddal lehet ezt megtenni. Erre alkalmasak egyes adatbáziskezelő rendszerekben az adatbázis triggerek (A trigger olyan tevékenységet definiál mely automatikusan végbemegy, ha egy tábla vagy nézet módosul vagy ha egyéb felhasználói vagy rendszeresemények következnek be. Azaz bármilyen változás az adatbázisban egy triggert indít el. A trigger egy adatbázis-objektum.)
A NOT NULL és a CHECK megszorítások ellenőrzése egyszerű, mivel csak a beszúrni kívánt sor értékeit kell megvizsgálni
A PRIMARY KEY és a UNIQUE megszorításokat az adatbáziskezelő rendszerek nagy része egyedi indexek segítségével biztosítja. Ilyenkor ezek az indexek a kényszerek létrehozásával párhuzamosan létrejönnek a háttérben.
A FOREIGN KEY kezelése adatbáziskezelő rendszer függő, hogy létrehoz-e indexet a háttérben, vagy sem.
A többfelhasználós környezet jelenti azonban az igazi kihívást, az egyidőben elvégzett módosítások ellenőrzése igen nehéz feladat.
Kényszerek eldobása történhet táblamódosítással, vagy az egész tábla eldobásával is.
Kényszerek eldobása történhet a teljes tábla eldobásával is.
Szintaxisa:
DROP TABLE táblanév;
Kényszereket dobhatunk el táblamódosításkor (DROP) vagy kikapcsolhatjuk őket ideiglenesen (DISABLE).
Táblamódosításnál a kényszerek kikapcsolhatók, ekkor ideiglenesen letiltjuk őket, vagyis a rendszer úgy tekinti, mintha nem is léteznének.
Példa: ha nagyon sok, a kényszereket nem sértő adatot importálunk be egyszerre, célszerű ideiglenesen kikapcsolni a kényszereket, így gyorsabb lesz a feldolgozás.
Szintaxisa:
ALTER TABLE táblanév DISABLE CONSTRAINT kényszernév;
Táblamódosítás során a kényszerek eldobhatók.
Szintaxisa:
ALTER TABLE táblanév DROP CONSTRAINT kényszernév;
A kényszerek vagy a tábladefiníció, vagy az oszlopdefiníció részei, ezért létezhetnek tábla és oszlopszintű kényszerek (adatbáziskezelő függő). Azonban fontos, hogy vannak olyan korlátozások, melyeket nem tudunk constraintek segítségével megadni.
A kényszereknek létrehozáskor nem kötelező, de célszerű nevet adni, mert ha szabályszegés történik, a hibaüzenetben a kényszer nevével fog hivatkozni a megsértett constraintre az adatbáziskezelő. Ha a rendszer generál egy kódsort névnek, sokkal bonyolultabb a kényszer visszakeresése.
Táblamódosítás során új kényszereket hozhatunk létre (ADD), vagy visszakapcsolhatjuk őket (ENABLE).
Táblamódosításnál a kényszerek bekapcsolhatók, illetve visszakapcsolhatók.
Szintaxisa:
ALTER TABLE táblanév ENABLE CONSTRAINT kényszernév;
Táblamódosításnál új kényszer adható hozzá.
Szintaxisa:
ALTER TABLE táblanév ADD CONSTRAINT kényszernév;
Kényszerek a tábla létrehozása során jönnek/jöhetnek létre.
Szintaxisa (példa):
CREATE TABLE táblanév(
oszlopnév1 adattípus CONSTRAINT kényszernév1 PRIMARY KEY
, oszlopnév2 adattípus CONSTRAINT kényszernév2 UNIQUE
, oszlopnév3 adattípus CONSTRAINT kényszernév3 NOT NULL,
, oszlopnév4 adattípus
, CONSTRAINT kényszernév4 CHECK (feltétel)
);
Az adatbáziskezelő figyeli a felhasználók által beírt DML utasításokat (INSERT, UPDATE, DELETE), ha ezek "szabályt szegnek", akkor azonnal megakadályozza a műveletet, sikertelen adatmódosítások lesznek.
Ezen szabályok betartását megnehezíti a többfelhasználós környezet és a tranzakciós műveletek.
Példa ilyen problémára: ha egy user beszúr egy adatot egy UNIQUE oszlopba, azt a commitálásig csak ő láthatja, a többiek nem, és így esetleg egy másik felhasználó is ugyanazzal az ID-val szeretne felvinni adatot.
Kényszerek ellenőrzése:
Egyes rendszerek elodázhatják az ellenőrzést a tranzakció végéig, így például be lehet vinni először a gyerek-adatot, majd utána a szülő-adatot
Egyes rendszerek a kényszereket azonnal a DML utasítás végén ellenőrzik, és ha megszegés történik, hibaüzenettel elutasítják
