Vortrag

Seminar Intelligente Datenbanken
Integritätsbedingungen in SQL
Syntax und Semantik
Bastian Kraemer
© Bastian Kraemer
Seminar IDB, 10.05.05
1
Quellen
“Semantic integrity support in SQL:1999 and commercial (object-)relational
database management systems”
von C. Türker und M. Gertz in VLDB J. 10(4): Seite: 241-269, 2001
“An Amateur’s Introduction to Integrity Constraints and Integrity Checking
in SQL”
Behrend A., Manthey R., Pieper B in BTW 2001: Seiten: 405-423 2001
Folien zur Vorlesung "Objektrelationale, erweiterbare Datenbanken„ von C. Türker,
ETH Zürich; WS 2002/2003; Kapitel 3: SQL:1999 – Der objektrelationale
Datenbankstandard
© Bastian Kraemer
Seminar IDB, 10.05.05
2
Überblick
1) Einführung
– Welche Unterschiede gibt es?
– Was muss beachtet werden?
2) Syntax
– Wie werden IB in SQL formuliert?
3) Semantik
– Welche Bedeutungen haben die verschiedenen Möglichkeiten?
– Wann sind IB erfüllt?
3) Ausblick
– Gibt es noch weitere Methoden, die SQL anbietet?
– Wie wurden die bisherigen Möglichkeiten umgesetzt?
4) Zusammenfassung
© Bastian Kraemer
Seminar IDB, 10.05.05
3
Einführung
• Integritätsbedingungen (IB) in SQL sind semantische IB:
– d.h. sie sind nur bezogen auf Eigenschaften der Miniwelt
– im Gegensatz zu Mechanismen zum Schutz der Datenkonsistenz
• Wohin „gehören“ IB?
– ins DBMS (+)
• nicht so fehleranfällig
• weniger Wartungsaufwand
– in Anwendungsprogramme (-)
• Anpassung muss mehrmals implementiert werden
• kurzfristiges globales Abschalten nicht möglich
© Bastian Kraemer
Seminar IDB, 10.05.05
4
Konsistenz
Wann werden IB verletzt?
– bei Veränderungen des Datenbankschemas
– bei Veränderungen der gespeicherten Daten
• Konsistenz auf der Transaktionsebene
– innerhalb einer Transaktion können inkonsistente Zustände
auftreten
– konsistenter Zustand muss aber am Ende wieder erreicht sein
• Konsistenz auf der Zustandsebene
– nach jeder Aktion muss die Datenbank in einem konsistenten
Zustand sein
© Bastian Kraemer
Seminar IDB, 10.05.05
5
Einteilung von IB
1. Einteilung basierend auf der Anzahl der beteiligten Zeilen
– zeileninterne Beschränkungen
z.B.: Gehalt + Spesen < 10000
– tabelleninterne Beschränkungen
z.B.: PRIMARY KEY
– tabellenübergreifende Beschränkungen
z.B.: FOREIGN KEY mit Referenz auf eine andere Tabelle
2. Einteilung nach Anzahl der zu betrachtenden Zustände
– statische IB
können durch genau einen Datenbankzustand verifiziert werden
– transitionelle IB
können nur mit zwei Datenbankzuständen überprüft werden
– dynamische oder temporale IB
mehr als zwei Datenbankzustände zur Verifizierung nötig
© Bastian Kraemer
Seminar IDB, 10.05.05
6
Syntax von IB in SQL:1999
Gemeinsamkeiten aller IB in SQL:
– eindeutiger Name
• explizit angegeben: CONSTRAINT <name>
• ohne Angabe wird vom System ein Name zugewiesen
– initialer Überprüfungsmodus
• DEFERRED: Überprüfung am Ende der Transaktion
• IMMEDIATE: Überprüfung unmittelbar nach jedem Zustandswechsel
– Verzögerungsmodus
• DEFERRABLE: späteres Umstellen auf DEFFERED ist möglich
• NOT DEFERRABLE: es bleibt immer bei IMMEDIATE
© Bastian Kraemer
Seminar IDB, 10.05.05
7
Überblick
• lokale IB:
Spaltenbeschränkungen
CREATE TABLE mitarbeiter (
id
INTEGER
PRIMARY KEY
name
VARCHAR(25) IS NOT NULL
gehalt INTEGER
CHECK ( > 0 AND < 10000)
...
Spaltendefinitionen
…
CHECK(gehalt + spesen < 25000)
...
);
Tabellenbeschränkungen
• globale IB:
– Assertions
– Wertebereichsbeschränkungen
© Bastian Kraemer
Seminar IDB, 10.05.05
8
Spaltenbeschränkungen
CREATE TABLE mitarbeiter (
Spaltenbeschränkungen
mnr
buero_nr
INTEGER
INTEGER
PRIMARY KEY,
UNIQUE,
name
VARCHAR(25)
NOT NULL,
vorgesetzter SMALLINT
abteilung
SMALLINT
Schlüsselbedingungen
Fremdschlüsselbedingungen
REFERENCES mnr,
REFERENCES abteilungen (id),
CHECK-Bedingungen
gehalt
DECIMAL(7,2)
NOT NULL
CHECK(gehalt > 2000),
CHECK( < 5000)
spesen
DECIMAL(7,2)
anstellung
DATE DEFAULT CURRENT_DATE,
);
implizite Spalte: spesen
© Bastian Kraemer
Seminar IDB, 10.05.05
9
Tabellenbeschränkungen
CREATE TABLE mitarbeiter (
...
UNIQUE (vorname, nachname),
CHECK (gehalt < 10000),
Tabellenbeschränkungen
mehrstellige Bedingungen
CONSTRAINT check_gesamtgehalt CHECK(gehalt + spesen < 25000)
CHECK (NOT EXISTS (SELECT * FROM projekte AS p
WHERE p.betreuer = name AND p.bonus > spesen))));
);
tabellenübergreifende Bedingungen
© Bastian Kraemer
Seminar IDB, 10.05.05
10
Referentielle Integrität
Fremdschlüsselbedingungen
Tabelle t1
S1
S2
A
1
C
4
H
6
NULL 9
S2 referenziert t2S2
S3
1
2
3
4
Tabelle t2
S1
t2S2
1
4
2
1
3
6
4
9
5
7
S3
C
A
H
NULL
J
Als Spaltenbeschränkung
Tabellenbeschränkungvon
in t1S2
(mehrstelliger
Schlüssel):
in t1:
FOREIGN
KEY (S1,
S2) REFERENCES
t2 (t2S2, S3)
S2
INTEGER
REFERENCES
t2 (t2S2)
Wann sollen zwei Zeilen übereinstimmen?
Was passiert bei Veränderungen in der referenzierten Tabelle?
© Bastian Kraemer
Seminar IDB, 10.05.05
11
Referentielle Integrität
Auf was wird verwiesen?
<ref-spec> ::=
REFERENCES <table-name>
( <column-list> )
[MATCH {SIMPLE | PARTIAL | FULL}]
[<ref-trig-action>]
<ref-trig-action> ::= ON UPDATE
[ON DELETE
| ON DELETE
[ON UPDATE
<ref-action>
<ref-action>]
<ref-action>
<ref-action>]
Wann wird reagiert?
<ref-action> ::=
© Bastian Kraemer
NO ACTION
| RESTRICT
| CASCADE
| SET NULL
| SET DEFAULT
Wie wird reagiert?
Seminar IDB, 10.05.05
12
Wertebereichsbeschränkung
CREATE DOMAIN hauptstaedte CHAR(30)
DEFAULT ’Berlin’
CHECK( VALUE IN (’Berlin’, ’Paris’, ’Rom’, ’London’)
);
CREATE TABLE land(
hauptstadt
hauptstaedte,
… );
CREATE TABLE land(
hauptstadt
DEFAULT ’Berlin’
CHECK( IN (’Berlin’, ’Paris’, ’Rom’,
’London’)
… );
© Bastian Kraemer
Seminar IDB, 10.05.05
13
Assertions
CREATE ASSERTION gesamt_jahresgehalt
CHECK(
(SELECT 12*SUM(gehalt + spesen) FROM mitarbeiter)
< 5000000
);
CREATE ASSERTION gehalt_untergrenze
CHECK (
NOT EXISTS (
SELECT *
FROM mitarbeiter
WHERE NOT (gehalt > 2000))
);
© Bastian Kraemer
Seminar IDB, 10.05.05
Allquantor
14
Semantik
Semantik von IB in SQL:1999
mögliche Vorgehensweisen:
direkt: pro Typ von IB Überprüfungsprinzip angeben
indirekt: durch Rückführung auf Assertions
Wertebereichsbeschränkungen
kein Existenzquantor, keine Variablen
Spaltenbeschränkungen
Kein Existenzquantor, keine Variablen
Tabellenbeschränkungen
CHECK
CHECK
Kein Existenzquantor, aber Variablen
Assertions
Existenzquantor und Variablen
© Bastian Kraemer
Seminar IDB, 10.05.05
15
Rückführung auf Assertions
CREATE TABLE t2 (…,
CONSTRAINT tc21 UNIQUE(a21,a22)
);
spezielle (Spalten-/)
Tabellenbeschränkungen
CREATE TABLE t2(…,
CONSTRAINT tc21‘ CHECK (NOT EXISTS
(SELECT * FROM t2 AS x WHERE
EXISTS (SELECT * FROM t2 AS y
WHERE x <> y
AND x.a21 = y.a21
AND x.a22 = y.a22)))
);
© Bastian Kraemer
Seminar IDB, 10.05.05
Tabellen-CHECKBedingungen
16
Rückführung auf Assertions
CREATE TABLE t3 (…,
CHECK ( a31 IN ( SELECT a43 FROM t4) )
);
Tabellenbeschränkungen
CREATE ASSERTION as1
CHECK (
NOT EXISTS (
SELECT a31
FROM t3 AS a31‘
WHERE a31‘ NOT IN
( SELECT a43 FROM t4 ) )
);
© Bastian Kraemer
Seminar IDB, 10.05.05
Assertions
17
Trigger
Ausblick auf Trigger
-
Seit SQL:1999 standardisierte Syntax
-
werden schon länger von kommerziellen DBMS unterstützt
- z.B. Sybase (seit 1987)
- dadurch keine einheitliche Syntax
-
bieten die Möglichkeit auf vordefinierte Ereignisse zu reagieren
(Damit lassen sich mit ihnen auch IB prüfen.)
-
aber: Probleme im Zusammenspiel zwischen Trigger und deklarativen
IB (Determinismus)
-
ermöglichen vor allem die Prüfung von dynamischen IB
© Bastian Kraemer
Seminar IDB, 10.05.05
18
Trigger
CREATE TRIGGER on_update_cascade_mitarbeiter
AFTER UPDATE OF id ON mitarbeiter
REFERENCING OLD AS alt NEW AS neu
Überprüfung
FOR EACH ROW
WHEN( EXISTS (SELECT * FROM abteilung WHERE leiter = alt.id))
BEGIN ATOMIC
UPDATE dept
SET leiter = neu.id
WHERE leiter = alt.id;
kompensierende Aktion
END;
© Bastian Kraemer
Seminar IDB, 10.05.05
19
Verzahnung von Triggern und deklarativen IB
SQL-Anweisung
↓
Bestimmen der Menge der betroffenen Objekte
↓
Ausführen der BEFORE-Trigger
↓
SQL-Anweisung ausführen (Änderungen werden wirksam)
↓
Überprüfung der deklarativen IB
↓
Ausführen der AFTER-Trigger
© Bastian Kraemer
Seminar IDB, 10.05.05
20
Überprüfung der deklarativen IB
…
↓
Überprüfe alle Fremdschlüssel mit der Einstellung RESTRICT!
↓
Überprüfe alle Fremdschlüssel mit den Einstellungen
CASCADE, SET NULL, SET DEFAULT!
↓
Überprüfe alle Fremdschlüssel mit der Einstellung NO ACTION
und alle anderen Integritätsbedingungen!
↓
…
© Bastian Kraemer
Seminar IDB, 10.05.05
21
Vergleich von IB in SQL und kommerziellen DBMS
SQL:
1999
Oracle
DB2
Informix
MS
SQL
Sybase
Ingres
Sybase
ANY
NOT NULL
√
√
√
√
√
√
√
√
DEFAULT
√
√
√
√
√
√
√
√
UNIQUE
√
√
√
√
√
√
√
√
PRIMARY KEY
√
√
√
√
√
√
√
√
SIMPLE
√
(√)
(√)
(√)
(√)
(√)
(√)
(√)
PARTIAL
√
-
-
-
-
-
-
-
FULL
√
-
-
-
-
-
-
-
NO ACTION
√
(√)
√
(√)
(√)
(√)
(√)
-
RESTRICT
√
-
√
-
-
-
-
√
CASCADE
√
√
√
√
-
-
-
√
SET NULL
√
√
√
-
-
-
-
√
SET DEFAULT
√
-
-
-
-
-
-
√
NO ACTION
√
(√)
√
(√)
(√)
(√)
(√)
-
RESTRICT
√
-
√
-
-
-
-
√
CASCADE
√
-
-
-
-
-
-
√
SET NULL
√
-
-
-
-
-
-
√
SET DEFAULT
√
-
-
-
-
-
-
√
FOREIGN KEY
MATCH
ON DELETE
ON UPDATE
© Bastian Kraemer
Seminar IDB, 10.05.05
22
Vergleich von IB in SQL und kommerziellen DBMS
DB2
Informix
MSSQL
Sybase
Ingres
Sybase
ANY
√
√
√
√
√
√
√
√
√
√
√
√
√
√
√
Tabellen
√
-
-
-
-
-
-
-
Datenbank
√
-
-
-
-
-
-
-
DOMAIN
√
-
-
-
-
-
-
√
ASSERTION
√
-
-
-
-
-
-
-
CHECK
√
(√)
SQL:
1999
Oracle
Spalten
√
Zeilen
= wie in SQL umgesetzt
= Konzept wie in SQL umgesetzt, aber mit anderer Syntax
Quelle: Semantic integrity support in SQL:1999 and commercial (object-)relational database management
systems. S. 12
© Bastian Kraemer
Seminar IDB, 10.05.05
23
Zusammenfassung
• Überblick über IB im Allgemeinen
• Syntax deklarativer IB in SQL
• Semantik deklarativer IB in SQL
– Zurückführung auf Tabellen- CHECK- Bedingungen und dann auf
Assertions
• Trigger zur Integritätserhaltung
• Realisierung in gängigen DBMS
© Bastian Kraemer
Seminar IDB, 10.05.05
24