Datenintegrität / referentielle Integrität
Folien
Falls keine PDF-Präsentation zu sehen ist: Direktdownload der PDF-Präsentation
Datenkonsistenz, Datenintegrität
Daten müssen sich in einem logisch korrekten, widerspruchsfreien Zustand befinden. Dann sind die Daten "integer" oder "konsistent".
Beispiel 1: In der folgenden Tabelle sind zwei verschiedene Schreibweisen eines Ortes zu ein und derselben Postleitzahl angegeben; der Arbeitgeber "Bäckerei Zimmermann" ist unterschiedlich erfasst; es fehlt ein Primärschlüssel, d.h. der dritte Datensatz kann nicht eindeutig identiiziert werden.
Beispiel 2: Welche Inkonsistenzen sind in dieser Datenbank zu finden?
Einige Fremdschlüssel weisen ins Nichts: postleitzahl 79312 hat keine Entsprechung in orte, es fehlt eine Tabelle arbeitgeber, auf die die Fremdschlüssel arbeitgeberFK verweisen können.
Integritätsregeln
Damit ein Datenbestand konsistent ist, müssen vier Integritätsbedingungen erfüllt sein:
- Bereichsintegrität: Attribute sind nur gültig, wenn sie einen bestimmten Wertebereich haben.
- Entitätsintegrität: Jeder Datensatz ist eindeutig definiert.
- Referentielle Integrität: Beziehungen zwischen Tabellen müssen synchronisiert bleiben.
- Benutzerdefinierte Integrität: Sonstige vom Benutzer festgelegte Regeln (z.B.: Datum darf nicht vor 01.01.2000 liegen)
Interessant für uns ist v.a. die referentielle Integrität, siehe unten. Bereichsintegrität und Entitätsintegrität ist für uns nur theoretisch interessant, da das RDBMS i.d.R. dafür sorgt, dass diese Regeln eingehalten werden (versuchen Sie mal, als Geburtsdatum "hihihi" einzugeben).
COMMIT / ROLLBACK
Eine Datenbank muss sich VOR einer Transaktion in einem konsistenten Zustand befinden, danach aber ebenfalls. Nun kann es gerade bei Änderungen an zahlreichen Datensätzen vorkommen, dass ein Fehler auftritt - dann sind einige Datensätze von der Änderung betroffen, andere nicht. Die Datenbank ist dann möglicherweise in einem inkonsistenten Zustand. Mit ROLLBACK kann die gesamte Transaktion dann wieder rückgängig gemacht werden (mit COMMIT wird sie bestätigt; das setzt in MySQL/InnoDB voraus, dass AUTOCOMMIT auf 0 steht (MySQL-Doku: AUTOCOMMIT)).
Klassisches Beispiel ist der Bankensektor: Zu einem bestimmten Zeitpunkt werden bspw. die Dispozinsen berechnet und dem Konto zugerechnet. Das betrifft sehr viele Kunden. Wenn nun ein Systemabsturz während der Änderung erfolgt, haben einige Kunden die Dispozinsen bereits zugerechnet, andere nicht. Hier hilft nur ein ROLLBACK.
Referentielle Integrität
Die referentielle Integrität besagt, dass Primärschlüssel-Fremdschlüssel-Beziehungen intakt sein müssen. Beispiel:
Die Definition von Wikipedia ist ganz gut, man könnte sie direkt für Klausuren oder das Abitur auswendiglernen:
Definition referentielle Integrität
Die referentielle Integrität (auch Beziehungsintegrität) besagt, dass Attributwerte eines Fremdschlüssels auch als Attributwert des Primärschlüssels vorhanden sein müssen.
Als Grundregel der referentiellen Integrität gilt:
Fremdschlüssel müssen IMMER auf existierende Datensätze verweisen!
Das muss beachtet werden beim
1. Löschen von Datensätzen - wenn dadurch bspw. in der Kindtabelle ein Fremdschlüssel verwaist:
2. Einfügen von Datensätzen - wenn dadurch bspw. in der Kindtabelle ein verwaister Fremdschlüssel entsteht:
Das gilt natürlich auch für das Ändern von Fremdschlüsseln.
Änderungsweitergabe, Löschweitergabe
Viele RDBMS haben Hilfsmittel, um die referentielle Integrität zu wahren. In MySQL kann InnoDB durch Foreign-Key-Constraints helfen.
Löschweitergabe
Wird ein Datensatz gelöscht, werden die abhängigen Datensätze auch in der Kindtabelle gelöscht. Beispiel: Wird vom Buchhändler ein Autor gelöscht, könnten sämtliche Bücher, die dieser Autor geschrieben hat, ebenfalls gelöscht werden.
Änderungsweitergabe
Wird ein Datensatz geändert, wird diese Änderung auch in der Kindtabelle ausgeführt. Beispiel:
Da wird über ON DELETE ... oder ON UPDATE ... erledigt. Entweder macht man sich die Hände direkt im MySQL-Code schmutzig und schreibt etwas wie
CREATE TABLE kunden (
id INT, name VARCHAR(45),postleitzahl_FK VARCHAR(5),
PRIMARY KEY (idkunden), -- ....
CONSTRAINT FK_postleitzahl
FOREIGN KEY (postleitzahl_FK)
REFERENCES orte (postleitzahl)
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB;... oder man lässt MySQL-Workbench für sich arbeiten. Beim Anlegen oder Ändern einer Tabelle (rechte Maustaste auf die Tabelle, "Alter Table ...") wählt man dann den Tab "Foreign Keys" und stellt die Weitergabeform ein:
Dabei gibt es für ON UPDATE ... oder ON DELETE ... folgende möglichen Werte:
CASCADE - Änderungen/Löschung auch in Kindtabelle vornehmen
SET NULL - Fremdschlüssel in Kindtabelle auf NULL setzen
NO ACTION - keine weitere Aktion
RESTRICT - Aktion verweigern
Links
zdnet: Zuverlässigere Datenbanken durch Fremdschlüssel in MySQL - ausführliche Beschreibung, wie bei MySQL durch Inno-DB die referentielle Integrität aufrechterhalten werden kann.
Übungen
Zusätzliche Dateien (Bilder, Klassendiagramme, HTML-Vorlagen) finden Sie in der Materialsammlung. Diese Übungen können Sie als txt-Datei herunterladen.
Übungen: Datenintegrität / Referentielle Integrität
DB_05-1: Definition
Definieren Sie den Begriff "referentielle Integrität" und erläutern Sie ihn anhand eines Beispiels.
DB_05-2: Verständnisfragen zur Referentiellen Integrität
Betrachten Sie die Datenbank, die in 06datenbanken/datenmodellierung05_datenintegritaet_aufgabe2.png abgebildet ist.
Welche Operationen sind zulässig?
a) Einfügen Datensatz
b) Einfügen Datensatz
c) Löschen Datensatz
d) Löschen Datensatz
DB_05-3: Anwendung von FOREIGN-KEY-CONSTRAINTS
Benutzen Sie für diese Aufgabe diesen Dump: 06datenbanken/datenmodellierung05_datenintegritaet_kundendump.sql
Achtung: Einige Aufgaben funktionieren vielleicht nicht. Versuchen Sie genau (!) zu verstehen, warum nicht (die Fehlermeldungen sind i.d.R. zu allgemein gehalten).
1. Löschen Sie alle Orte (DELETE FROM orte;)
2. Wenden Sie RESTRICT auf ON UPDATE und ON DELETE an.
3. Löschen Sie via SQL-Befehl den Ort Emmendingen (DELETE FROM orte WHERE ...).
4. Ändern Sie die Postleitzahl von Musterhausen (UPDATE orte SET postleitzahl = 99999 WHERE ...).
5. Sie haben festgestellt, dass sich Änderungen nicht durchführen lassen. Ändern Sie die FOREIGN-KEY-CONSTRAINTS und probieren Sie sie bspw. mit Nr. 3 und 4 oben aus.