Integritné obmedzenia V sql jan Milan Možnosti zabezpečenia integrity

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

• 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’, ‘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 Filmy1 (

• Nazov CHAR(30) PRIMARY KEY )

• CREATE TABLE Filmy2 (

• 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 Filmy1 )

Klauzula FOREIGN KEY

• CREATE TABLE Komedie (

• Nazov_filmu CHAR(30),

• Meno_rezisera CHAR(50),

• FOREIGN KEY (Nazov_filmu, Meno_rezisera)

• REFERENCES Filmy2(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

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“

• Oracle9i SQL Reference Release 2

• Slidy ku školeniu Oracle 8i – SQL