Kényszerek

Lisää tämän kaltaisia

Tranzakcionálisan konzisztens működés, zárak, a működés naplózása

luonut Zsolt Vajda

Adatbázis fizikai szerkezete, adatszótár és monitorozás

luonut Andrea Horváth

Tranzakcionálisan konzisztens működés, zárak és működés naplózása

luonut Kinga Tajti

Adatbázis-rendszer választásának szempontjai

luonut Joci Csépányi

Adatbázis kényszerek

Típusai

CREATE TABLE JATEKOSOK(

ID NUMBER(3)

,VNEV VARCHAR2(16) NOT NULL

,KNEV VARCHAR2(16) CONSTRAINT jatekos_knev NOT NULL

,MEZSZAM NUMBER(2) CHECK

,CSAPAT NUMBER(3) REFERENCES CSAPAT (TID)

,CONSTRAINT jatekos_id_pk PRIMARY KEY(ID)

,CONSTRAINT jatekos_csapat_fk FOREIGN KEY (ID) REFERENCES CSAPAT(TID)

);

CREATE TABLE CSAPAT(

TID NUMBER(3)

,NEV VARCHAR2(25) UNIQUE

,ALAPITAS DATE

,CONSTRAINT csapat_tid_pk PRIMARY KEY (TID)

);

------------------------------------------------------------------------

Előadás alapján példa 7. lehetőségre:

CREATE TABLE JATEKOSOK(

ID NUMBER(3) CONSTRAINT jatekos_id PRIMARY KEY

,VNEV VARCHAR2(16) NOT NULL

,KNEV VARCHAR2(16) CONSTRAINT jatekos_knev NOT NULL

,MEZSZAM NUMBER(2)

,CSAPAT NUMBER(3)

,CONSTRAINT csapatid_fk FOREIGN KEY (csapat) REFERENCES csapat

);

Forrás: saját példa

A jatekosok tábla csapat oszlopa hivatkozik a csapat tábla tid oszlopára, csak olyan értékeket írhatok a csapat oszlopba, ami a tid-ben is megtalálható:

CREATE TABLE JATEKOSOK(

ID NUMBER(3) PRIMARY KEY

,VNEV VARCHAR2(16) NOT NULL

,KNEV VARCHAR2(16) CONSTRAINT jatekos_knev NOT NULL

,MEZSZAM NUMBER(2) CHECK

,CSAPAT NUMBER(3) REFERENCES CSAPAT (TID)

);

CREATE TABLE CSAPAT(

TID NUMBER(3) PRIMARY KEY

,NEV VARCHAR2(25) UNIQUE

,ALAPITAS DATE

);

Forrás: saját

Csak táblázat feltételnél adhatjuk meg.

Master-Detail kapcsolat

ON DELETE CASCADE:

"Definiálja, hogy ha az idegen kulccsal hivatkozott rekordot törlik a hivatkozott táblázatból, akkor az összes rá hivatkozó rekord is törlődik a hivatkozó táblázatból. Ha nem adjuk meg sem ezt, sem az ON DELETE SET NULL klauzulát, akkor a hivatkozott táblázatból olyan rekordot, amelyikre hivatkozás van, nem lehet törölni."

Forrás: Quittner - Baksa-Haskó: Adatbázisok, adatbázis-kezelő rendszerek

UNIQUE KEY

Ugyanaz, mint az előbb, csak out-of-line adom meg:

CREATE TABLE CSAPAT(

TID NUMBER(3) PRIMARY KEY

,NEV VARCHAR2(25)

,ALAPITAS DATE

,CONSTRAINT csapat_nev_uq UNIQUE(NEV)

);

A nev oszlopban csak egyedi értékek megengedettek:

CREATE TABLE CSAPAT(

TID NUMBER(3) PRIMARY KEY

,NEV VARCHAR2(25) UNIQUE

,ALAPITAS DATE

);

Forrás: saját

Olyan kulcs, amely nem a tábla elsődleges kulcsa. Kizárólag egyedi értékeket enged meg.

NULL értékkel nem szegjük meg az egyediséget.

Forrás: előadás

PRIMARY KEY

Maximum egy ilyen lehet egy táblában. Egyedi értéknek kell lennie, vagyis nem lehet két azonos sor. Nem tartalmazhat NULL értéket.

Forrás: előadás

CREATE TABLE JATEKOSOK(

ID NUMBER(3)

,VNEV VARCHAR2(16) NOT NULL

,KNEV VARCHAR2(16) CONSTRAINT jatekos_knev NOT NULL

,NEM VARCHAR2(1) CHECK (NEM='F' OR NEM='N')

,MEZSZAM NUMBER(2) CHECK

,CSAPAT NUMBER(3) REFERENCES CSAPAT (TID)

,CONSTRAINT jatekos_id_pk PRIMARY KEY(ID)

);

CREATE TABLE CSAPAT(

TID NUMBER(3)

,NEV VARCHAR2(25) UNIQUE

,ALAPITAS DATE

,CONSTRAINT csapat_tid_pk PRIMARY KEY (TID)

);

Forrás: saját példa

CREATE TABLE JATEKOSOK(

ID NUMBER(3) CONSTRAINT jatekos_id_pk PRIMARY KEY

,VNEV VARCHAR2(16) NOT NULL

,KNEV VARCHAR2(16) CONSTRAINT jatekos_knev NOT NULL

,NEM VARCHAR2(1) CHECK (NEM='F' OR NEM='N')

,MEZSZAM NUMBER(2)

,CSAPAT NUMBER(3) REFERENCES CSAPAT (TID)

);

CREATE TABLE CSAPAT(

TID NUMBER(3) PRIMARY KEY

,NEV VARCHAR2(25) UNIQUE

,ALAPITAS DATE

);

Forrás: saját példa

Többoszlopos (konkatenált)

Összetett kulcsnak is nevezik.

A sorszam+datum oszlop összetett kulcs:

CREATE TABLE foglalas(

sorszam NUMBER(4)

,datum DATE DEFAULT TRUNC(SYSDATE) NOT NULL

,nev VARCHAR2(50)

,ar NUMBER(6)

,CONSTRAINT uq_sorsz_date UNIQUE (sorszam, datum)

);

Forrás: előadás + saját példa

Egyoszlopos

CHECK

out-of-line

CREATE TABLE JATEKOSOK(

ID NUMBER(3) CONSTRAINT jatekos_id PRIMARY KEY

,VNEV VARCHAR2(16) NOT NULL

,KNEV VARCHAR2(16) CONSTRAINT jatekos_knev NOT NULL

,NEM VARCHAR2(1)

,MEZSZAM NUMBER(2) CHECK

,CSAPAT NUMBER(3) REFERENCES CSAPAT (TID)

,CONSTRAINT jatekos_nem CHECK (NEM='F' OR NEM='N')

);

CREATE TABLE CSAPAT(

TID NUMBER(3)

,NEV VARCHAR2(25) UNIQUE

,ALAPITAS DATE

);

CREATE TABLE JATEKOSOK(

ID NUMBER(3) CONSTRAINT jatekos_id PRIMARY KEY

,VNEV VARCHAR2(16) NOT NULL

,KNEV VARCHAR2(16) CONSTRAINT jatekos_knev NOT NULL

,NEM VARCHAR2(1) CHECK (NEM='F' OR NEM='N')

,MEZSZAM NUMBER(2) CHECK

,CSAPAT NUMBER(3) REFERENCES CSAPAT (TID)

);

A nem oszlopban csak F és N értékeket engedek meg.

Forrás: saját példa

Tábla egy sorára vonatkozik. Logikai kifejezés, külön-külön minden sorra igaznak kell lennie.

Forrás: előadás

NOT NULL

Példák

inline

Két táblából álló adatbázis, amely kézilabda játékosokat és csapatokat tartalmaz:

CREATE TABLE JATEKOSOK(

ID NUMBER(3) CONSTRAINT jatekos_id PRIMARY KEY

,VNEV VARCHAR2(16) NOT NULL

,KNEV VARCHAR2(16) CONSTRAINT jatekos_knev NOT NULL

,NEM VARCHAR2(1) CHECK (NEM='F' OR NEM='N')

,MEZSZAM NUMBER(2)

,CSAPAT NUMBER(2) REFERENCES CSAPAT (TID)

);

CREATE TABLE CSAPAT(

TID NUMBER(2)

,NEV VARCHAR2(25) UNIQUE

,ALAPITAS DATE

);

Forrás: saját példa

Jellemzői

A tábla egy oszlopára vonatkozik.

Nem engedélyezi az adott oszlopban a NULL értéket.

A CHECK speciális esetének is tekinthető.

Forrás: előadás diái

Általánosságban

Problémák is felmerülhetnek.

Tranzakciós működési probléma:

Ha több felhasználó dolgozik egy adatbázison a változtatások csak commitolás után válnak láthatóvá a többieknek.

Forrás: előadáson elhangzott

Deklarációk

DDL nyelv részei

Táblákra és azok oszlopaira vonatkozó szabályok.

Ha adatmódosítással szabályt szegnénk, akkor azt azonnal megakadályozza.

Forrás: előadás

Az adatbázisok tartalmát jellemezzük velük.

Kényszerek biztosítása

FOREIGN KEY

Egyes adatbáziskezelő rendszereknél kötelező, míg másoknál ajánlott az index.

"Ahhoz, hogy egy táblázatra idegen kulccsal hivatkozhassunk, a táblázatnak léteznie kell, a hivatkozott oszlopokon elsődleges kulcs vagy egyedi index kell legyen és REFERENCES jogosultságunk kell legyen rá."

Forrás: előadás

Quittner - Baksa-Haskó: Adatbázisok, adatbázis-kezelő rendszerek

PRIMARY KEY és UNIQUE

Automatikusan létrejött egyedi indexek biztosítják.

NOT NULL és CHECK

Beszúrni kívánt sor értékeinek az ellenőrzése.

Forrás: előadás

Ellenőrzés

A kényszereket a DML utasítás végén ellenőrzik az adatbáziskezelő rendszerek. Ha megszegnénk a kényszert a DML uatsítást hibaüzenettel elutasítják.

Ha előbb a szülő táblába, majd utána a gyerek táblába visszük be az adatokat -> sikerül

Viszont ha fordítva akarom bevinni az adatokat, akkor hibaüzenetet ír ki:

"a szülő kulcs nem található".

Hibaüzenet (kép):

https://flic.kr/p/SMv1Ns

Ha az adatok bevitelekor kihagyom az oszlopot (megengedi a NULL értéket), ami a másik táblára hivatkozik, sikerül az adatok beszúrása.

Forrás: előadás + saját

Eldobásuk

foreign key esetén

PRIMARY KEY és UNIQUE kényszer nem dobható el, ha FOREIGN KEY megszorításban hivatkozunk rá.

Szülő tábla sem dobható el.

Hibaüzenet (kép):

https://flic.kr/p/T2gj8K

Az hibaüzenet alapján nem sikerült eldobni az elsődleges kulcsot, mivel más idegen kulcsok hivatkoznak rá.

Forrás: előadás + saját

A mufajok tábla nev oszlophoz tartozó UNIQUE kényszert eldobom:

ALTER TABLE mufajok DROP CONSTRAINT nev_uq;

----------------------------------------------

Kényszer megszüntetése:

ALTER TABLE filmek DISABLE CONSTRAINT mufaj_fk;

Forrás: saját példa

teljes tábla eldobásával

DROP TABLE utasítással

Filmek tábla eldobása:

DROP TABLE filmek;

Ha olyan táblát próbálnék eldobni, aminek az elsődleges kulcsa egy másik táblának az idegen kulcsa, nem sikerülne.

Hibaüzenet (kép):

https://flic.kr/p/RMyNMz

(Megpróbáltam eldobni a csapat táblát, de az id oszlop a jatekosok tábla egyik oszlopának az idegen kulcsa.)

Forrás: előadás, saját

Létrehozásuk

táblakényszer

foreign key

oszlopszintű kényszer

primary key

not null

tábla módosításával

Kezdetben létrehoztam két táblát (mufajok és filmek), majd ezeket fogom módosítani:

CREATE TABLE mufajok(

azon NUMBER(2) PRIMARY KEY

,nev VARCHAR2(16) NOT NULL

,CONSTRAINT nev_uq UNIQUE (nev)

);

CREATE TABLE filmek(

id NUMBER(2)

,cim VARCHAR2(25) NOT NULL

,rendezo VARCHAR2(50) NOT NULL

,ev DATE

,mufaj NUMBER(2)

,CONSTRAINT id_pk PRIMARY KEY(id)

,CONSTRAINT mufaj_fk FOREIGN KEY(mufaj) REFERENCES mufajok (azon)

);

A Filmek tábla módosításával a cim oszlophoz hozzáadok egy NOT NULL kényszert:

ALTER TABLE filmek ADD (

CHECK (cim IS NOT NULL)

);

----------------------------------------------

Kényszer engedélyezése (feltéve, hogy előtte megszüntettem a kényszert):

ALTER TABLE filmek ENABLE CONSTRAINT mufaj_fk;

Forrás: saját példa

a tábla létrehózásakor

CREATE TABLE filmek(

id NUMBER(2)

,cim VARCHAR2(25)

,rendezo VARCHAR2(50) NOT NULL

,ev DATE

,mufaj NUMBER(2)

,CONSTRAINT id_pk PRIMARY KEY(id)

);

Forrás: saját példa