Integritn obmedzenia v SQL Jan Milan Monosti zabezpeenia

Integritné obmedzenia v SQL Jan Milan

Možnosti zabezpečenia integrity • Aplikačná vrstva – Každá funkcia samostatne – Integrita sa neudržuje a dojem konzistencie sa vytvára až na prezenčnej vrstve – Aplikácie, ktoré periodicky kontrolujú stav DB • Stredná vrstva – Spoločné funkcie na úrovni aplikácie alebo v DB • Na úrovni Databáze – Integritné obmedzenia

Typy obmedzení (v SQL) • Domenové • Tabuľkové • Obecné (nahraditeľné pomocou tabuľkových)

Doménové obmedzenia • Typové obmedzenia CHAR(10), NUMBER(2, 7) CREATE DOMAIN T_Farba CHAR(10) DEFAULT ‘? ? ? ’ CONSTRAINT Platne_farby CHECK ( VALUE IN (‘Modra’, ‘Zlta’, ‘? ? ? ’)) • Pohľady CREATE VIEW Modre_predmety AS SELECT * FROM Predmety WHERE Farba = ‘Modra’ WITH CHECK OPTION

Tabuľkové obmedzenia • Sú súčasťou príkazov pre vytváranie tabuliek alebo zmenu ich definície – NOT NULL – UNIQUE – CHECK – PRIMARY KEY – FOREIGN KEY

Klauzula NOT NULL • Zajisťuje, že daný stĺpec bude vždy obsahovať hodnotu CREATE TABLE Filmy ( Nazov CHAR(30) NOT NULL, Meno_rezisera CHAR(50), …) • Nahraditeľná pomocou CHECK ( Nazov IS NOT NULL )

Klauzula UNIQUE • Zabezpečuje unikátnosť každej hodnoty v stĺpci • Nevylučuje nezadanú hodnotu (NULL), može sa však vyskytovať iba raz • Dá sa definovať na úrovni stĺpca aj tabuľky CREATE TABLE Dodavatelia ( Nazov CHAR(30) UNIQUE, …) CREATE TABLE Ocenenia ( Nazov_filmu CHAR(30) NOT NULL, Meno_herca CHAR(30) NOT NULL, UNIQUE (Nazov_filmu, Meno_herca) )

Klauzula CHECK • Určuje podmienku, ktorú musí splniť každý stĺpec • obmedzenia podmienky – Nemože sa odkazovať na iné riadky danej tabuľky (može však obsahovať poddotazy) – Nemala by obsahovať funkcie, ktorých hodnota závisí na konkrétnom čase alebo prihlásenom uživateľovy

Klauzula CHECK CREATE TABLE Filmy ( Nazov CHAR(30), Cena NUMBER(9, 2) CHECK (Cena < 100), Meno_rezisera CHAR(50) ) CREATE TABLE Filmy ( Nazov CHAR(30), Cena NUMBER(9, 2), Meno_rezisera CHAR(50), CHECK (Cena BETWEEN 0 AND 100) )

Klauzula CHECK CREATE TABLE Filmy ( Nazov CHAR(30), Typ CHAR(10) DEFAULT ‘? ? ? ’, Natoceny CHAR(3), Na_kazetach CHAR(3), CHECK (Typ IN (‘Komedia’, ’Akcny’, ’? ? ? ’)), CHECK ((Natoceny, Na_kazetach) IN ( (‘Nie’, ‘Nie’), (‘Ano’, ‘Ano’)) )

Klauzula CHECK CREATE TABLE Detske_filmy ( Nazov CHAR(30), Odporucana_cena NUMBER(5, 2), Predajna_cena NUMBER(5, 2), CHECK (Nazov NOT IN (SELECT Nazov FROM Filmy_nevhodne_pre_deti)), CHECK (Predajna_cena > Odporucana_cena) )

Klauzula PRIMARY KEY • Určuje stĺpec (resp. stĺpce), ktorý je primárnym kľúčom tabulky • Alternatívne kľúče pomocou kombinácie NOT NULL a UNIQUE CREATE TABLE Filmy 1 ( Nazov CHAR(30) PRIMARY KEY ) CREATE TABLE Filmy 2 ( Nazov CHAR(30), Meno_rezisera CHAR(50), PRIMARY KEY (Nazov, Meno_rezisera))

Klauzula FOREIGN KEY • Zajisťuje referenčnú integritu • Stĺpec (resp. Skupina stĺpcov), ktorého hodnota závisí na hodnotách v inej tabuľke • Obsahuje aj definíciu akcií, ktoré sa vykonajú pri zmenách v referencovanej tabuľke CREATE TABLE Komedie ( Nazov_filmu CHAR(30) REFERENCES Filmy 1 )

Klauzula FOREIGN KEY CREATE TABLE Komedie ( Nazov_filmu CHAR(30), Meno_rezisera CHAR(50), FOREIGN KEY (Nazov_filmu, Meno_rezisera) REFERENCES Filmy 2(Nazov, Meno_rezisera)) CREATE TABLE Zamestnanci ( ID_Zamestnanca NUMBER PRIMARY KEY, Meno CHAR(50), ID_Nadriadeneho NUMBER REFERENCES Zamestnanci)

Akcie referenčného obmedzenia • Akcie iba při referenčnom omezení • Spúšťa ich zmena alebo zmazanie hodnoty v referencovanej tabuľke • Kľúčové slová – ON UPDATE – ON DELETE • • CASCADE SET NULL SET DEFAULT NO ACTION ID_Odd 1 2 3 ID_Zam 1 2 3 4 Meno AAA BBB CCC DDD Nazov Odd 1 Odd 2 Odd 3 ID_Odd 2 2 3 1

Akcie referenčného obmedzenia CREATE TABLE Zamestanci ( ID_Zam NUMBER NOT NULL, Meno CHAR(50), ID_Odd NUMBER, CONSTRAINT Zam_Odd_fk FOREIGN KEY (ID_odd) REFERENCES Oddelenia(ID_odd) ON UPDATE CASCADE ON DELETE SET NULL )

Tabuľkové obmedzenia obecne • Používanie kľúčoveho slova CONSTRAINT • Indexy

Obecné obmedzenia • • Kľúčové slovo ASSERTION Niesú spojené s jednou tabuľkou Samostatné objety databáze Nahraditeľné pomocou tabuľkových, klauzulou CHECK • Tabuľkové obmedzenia sa uplatňujú iba ak daná tabuľka obsahuje dáta

Obecné obmedzenia CREATE TABLE. . . CHECK ((SELECT MAX(COUNT(ID_Zam)) FROM Zamestanci GROUP BY ID_Odd) < (SELECT COUNT(*) FROM Oddelenia)) ) CREATE ASSERTION Max_Zam_v_Odd CHECK (. . . ) CREATE TABLE Neprazdna (. . . CHECK((SELECT COUNT(*) FROM Neprazdna)>0)) CREATE ASSERTION Neprazdna_neprazdna CHECK((SELECT COUNT(*) FROM Neprazdna)>0)

Ďalšie vlastnosti [ [[NOT] DEFERRABLE] [INITIALLY { IMMEDIATE | DEFERRED }] [ ENABLE|DISABLE ] [ VALIDATE|NOVALIDATE ]

Ďalšie možnosti • Trigger

Zhrnutie • Možnosti zabezpečenia integrity • Typy obmedzení a ich vlastnosti – Doménové – Tabuľkové – Obecné

Použité zdroje • Jim Melton, Alan R. Simon: „Understanding the new SQL: A complete guide“ • „An introduction to Database Systems“ • Oracle 9 i SQL Reference Release 2 • Slidy ku školeniu Oracle 8 i – SQL