Referentielle Integrität

Die referentielle Integrität – oft auch als RI abgekürzt – ist eine Form der Datenintegrität. Unter der referentiellen Integrität versteht man die Integrität auf Beziehungsebene. Neben der referentiellen Integrität unterscheidet man noch die Wertebereichsintegrität (Integrität auf Datenfeldebene) und die Datenintegrität auf Datensatzebene.

Definition

Die referentielle Integrität befasst sich mit der Korrektheit zwischen Attributen von Relationen und der Erhaltung der Eindeutigkeit ihrer Schlüssel.

Die referentielle Integrität gewährleistet, dass in einer Tabelle2, die mit einer Tabelle1 in Beziehung steht, die Fremdschlüssel nur Werte annehmen können, die in der referenzierten Tabelle1 der Beziehung bereits als Attributwerte des Primärschlüssels vorhanden sind. Die Tabelle1 wird dabei häufig als Mastertabelle, Tabelle2 als Detailtabelle bezeichnet. Diese Bezeichnung kann irreführend beziehungsweise sogar falsch sein, weshalb hier Vorsicht geboten ist. Zu anderen Bezeichnungen s. unten.

Verwendung in Datenbanksystemen

Die Beziehungen werden zuvor in einem Datenbanksystem festgelegt. Das Datenbanksystem wird dann die Eigenschaften dieser Beziehungen zwischen den Relationen garantieren. Lösch- oder Änderungsoperationen, die die Integrität verletzen würden, werden von der Datenbank nicht ausgeführt – eine zugehörige Transaktion wird zurückgefahren. So werden Inkonsistenzen im Datenbestand verhindert, d. h. werden in einer Datenbank einzelne Tabellen mit referentieller Integrität verbunden, so kontrollieren sich die Daten gegenseitig. Beispiel: In einer Tabelle, die Warenbewegungen erfasst, können nur dann Bewegungen neuer Waren aufgenommen werden, wenn zuvor der entsprechende Artikel mit seiner Artikelnummer (= Primärschlüssel) erfasst wurde. Wird der Artikel gelöscht, müssen auch die Warenbewegungen in der Bewegungstabelle gelöscht werden, da sonst die Datenbank inkonsistent wird (allerdings wird man mit der Löschung sinnvollerweise warten, bis keine Lagerbestände mehr vorhanden sind und Auswertungen auf Grundlage dieser Tabellen nicht mehr erforderlich sind).

Technisch wird die referentielle Integrität zwischen zwei Relationen über einen so genannten Fremdschlüssel realisiert. Beide Relationen benötigen ein gemeinsames Attribut, das in der einen Relation ein Primärschlüssel und in der anderen Relation vom selben oder einem kompatiblen Datentyp sein muss. Die zweite Relation verweist („referenziert“) über diesen Fremdschlüssel auf den Primärschlüssel. Die Datenbank stellt sicher, dass dieser Primärschlüssel existiert und nur gemeinsam mit dem Fremdschlüssel geändert oder gelöscht werden kann.

Im Zusammenhang mit Tabellen bezeichnet man die Tabelle, auf deren Primärschlüssel verwiesen wird, als Parent-Tabelle. Die Tabelle, die den Fremdschlüssel enthält, bezeichnet man als Child-Tabelle. In der Literatur werden auch manchmal die deutschen Bezeichnungen Eltern-Tabelle und Kind-Tabelle sowie Mastertabelle und Detailtabelle (bitte obigen Warn-Hinweis beachten!) oder abhängige Tabelle verwendet.

Für Aktualisierungen des Primärschlüssels in der Parent-Tabelle sind folgende Optionen möglich:

• ON UPDATE CASCADE (Aktualisierungsweitergabe) bedeutet, dass die Aktualisierung auch an den Fremdschlüsseln der Child-Tabelle vorgenommen wird.
• ON UPDATE RESTRICT (Aktualisierungsrestriktion) bedeutet: Wenn ein Schlüssel in der Parent-Tabelle geändert werden soll, und es existieren abhängige Sätze in der Child-Tabelle, dann wird die Änderung verweigert.

Für Löschungen in der Parent-Tabelle sind folgende Optionen möglich:

• ON DELETE CASCADE (Löschweitergabe) bedeutet, es werden auch alle Sätze in der Child-Tabelle gelöscht, die auf diesen Schlüssel referenzieren.
• ON DELETE RESTRICT (Löschrestriktion) bedeutet: wenn ein Satz in der Parent-Tabelle gelöscht werden soll und es existieren abhängige Sätze in der Child-Tabelle, dann wird die Löschung verweigert.
• ON DELETE SET NULL (Nullifies) bedeutet: wenn ein Satz in der Parent-Tabelle gelöscht werden soll und es existieren abhängige Sätze in der Child-Tabelle, dann wird in diese Fremdschlüssel NULL eingetragen. Voraussetzung ist hier, dass der Fremdschlüssel als optional-Spalte in der Tabelle definiert ist (nicht mit NOT NULL).

Die verschiedenen Update- und Lösch-Optionen werden nicht von allen RDBMS unterstützt. Beispielsweise wird die Option ON UPDATE CASCADE nicht von allen RDBMS (z. B. Oracle) angeboten. Der Grund dafür ist die Sichtweise, dass ein Primärschlüssel konstant und nicht veränderbar sein sollte.

Für die Child-Tabelle bedeutet ein Fremdschlüssel die Restriktion, dass in diese Spalten nur Werte eingefügt (INSERT, UPDATE) werden dürfen, die in der Parent-Tabelle auch vorkommen. Einzige Ausnahme ist, wenn die Fremdschlüssel-Spalte als Optional-Spalte definiert ist. Dann kann hier auch NULL eingefügt werden, obwohl NULL niemals als Primärschlüssel in der Parent-Tabelle stehen wird.

Beispiel

Annahme, es wurden zwei Tabellen wie folgt in einer Datenbank angelegt:

Kunden
Kundennr_pk	Name	Ort	Bestellung
1	Meier AG	Marburg
2	Müller Bank	Waldhausen	*
3	Fa. Schaffel	Woauchimmer
4	Kinder KG	Kleckerdorf	*

Bestellungen
Bestellnr	Kundennr_fk
1	2
2	4

Weiter wurde definiert, dass die Kundennr in der Tabelle Kunden der Primärschlüssel und in der Tabelle Bestellungen der Fremdschlüssel (Foreign Key) ist.

Das Datenbankmanagementsystem sorgt dafür, dass:

• keine neue Bestellung ohne vorhandene Kundennr eingetragen werden kann.
• keine Kundensätze gelöscht werden können solange noch Bestellungen vorliegen. (Mit * markiert). Wurde allerdings die Löschweitergabe (DELETE CASCADE) eingestellt, werden alle Bestellsätze zum Kunden und der Kundensatz gelöscht.
• keine Kundennummern geändert werden können, ohne die abhängigen Bestellsätze mitzuändern (Update-Weitergabe) oder dass Änderungen an Kundennummern (bei den mit * markierten Sätzen) erst gar nicht zugelassen werden (Update-Restriktion).

SQL-Kommando zum Erstellen der Beziehung:

ALTER TABLE "Bestellungen" ADD CONSTRAINT fk_bestellungen_kundennr
FOREIGN KEY (kundennr_fk) REFERENCES "Kunden"(kundennr_pk)
  ON UPDATE RESTRICT
  ON DELETE RESTRICT;

Fremdschlüssel aus mehreren Attributen

Primärschlüssel und Fremdschlüssel können auch aus mehreren Spalten bestehen. Beispiel:

CREATE TABLE p(
    p1 INTEGER NOT NULL,
    p2 INTEGER NOT NULL,
    PRIMARY KEY (p1, p2)
);
 
CREATE TABLE d(
    d1 INTEGER NOT NULL,
    d2 INTEGER,
    d3 INTEGER,
    PRIMARY KEY (d1),
    FOREIGN KEY (d2, d3) REFERENCES p(p1, p2) ON DELETE CASCADE
);

Darstellung in Datenmodellen und Diagrammen

Leider wird die Pfeilrichtung bei einer graphischen Darstellung von den verschiedenen Autoren unterschiedlich verwendet. Einige zeichnen die Pfeile von der Mastertabelle zur Detailtabelle ein.

  Kunden (Mastertabelle)
     |
     V
  Bestellungen (Detailtabelle)

Andere gehen von den Abhängigkeiten aus, die in der Datenbank definiert sind: der Fremdschlüssel in der Detailtabelle verweist auf den Primärschlüssel in der Mastertabelle.

  Kunden (Mastertabelle)
     ^
     |
  Bestellungen (Detailtabelle)

Kombination von Fremdschlüsseln

Grundsätzlich kann eine Detailtabelle gleichzeitig als Mastertabelle für eine andere Tabelle definiert werden. Dann sind aber nicht alle Kombinationen von Lösch-Weitergabe und Lösch-Restriktion zulässig (Ebenso bei der Update-Option). Das RDBMS prüft beim Ausführen der DDL-Befehle, ob die gewünschte Regel zulässig ist.

Nicht zulässig ist:

  Tabelle A (Mastertabelle)
     ^
     | mit Löschweitergabe
     |
  Tabelle B (Detailtabelle von A)
     ^
     | mit Lösch-Restriktion
     |
  Tabelle C (Detailtabelle von B)

Zulässig ist:

  Tabelle A (Mastertabelle)
     ^
     | mit Lösch-Restriktion
     |
  Tabelle B (Detailtabelle von A)
     ^
     | mit Löschweitergabe
     |
  Tabelle C (Detailtabelle von B)

Rekursive Fremdschlüssel

Fremdschlüsselbeziehungen können auch rekursiv definiert werden. Dabei verweist eine abhängige Spalte einer Tabelle auf den Primärschlüssel der eigenen Tabelle.

Beispiel:

CREATE TABLE "Abteilung"(
    "AbtNr"             INTEGER NOT NULL,
    "UebergeordneteAbt" INTEGER,
    "AbtName"           VARCHAR(100),
    PRIMARY KEY ("AbtNr"),
    FOREIGN KEY ("UebergeordneteAbt") REFERENCES "Abteilung"("AbtNr")
        ON DELETE CASCADE
);

Rekursive Fremdschlüsselbeziehungen sind etwas problematisch in der Handhabung. Wenn Sätze eingefügt werden, dann muss das in einer bestimmten Reihenfolge geschehen.

Wenn Sätze gelöscht werden, dann kann es – bei Löschweitergabe – zur Löschung von wesentlich mehr Daten kommen, als in der WHERE-Bedingung angegeben:

DELETE FROM "Abteilung" WHERE "AbtNr" = 33;

Es werden bei diesem Beispiel auch alle Sätze gelöscht, die der Abteilung 33 untergeordnet sind.

Wenn der Fremdschlüssel mit Lösch-Restriktion definiert wurde, dann wird nach jeder Löschung eines einzelnen Satzes geprüft, ob es keine Fremdschlüsselverletzung gibt. Selbst wenn alle Sätze aus der Tabelle entfernt werden sollen, kann es passieren, dass die Ausführung fehlschlägt.

DELETE FROM "Abteilung";

Das liegt daran, dass bei der Ausführung des DELETE-Statements die Sätze in einer beliebigen Reihenfolge gelöscht werden, meistens in der Reihenfolge, in der sie im Tablespace gespeichert sind. Nur wenn die Sätze exakt in der richtigen Reihenfolge (von der untersten Abteilung beginnend bis zur obersten Abteilung) gespeichert sind, dann kann die Ausführung des DELETE-Statements gelingen.

Dass der Erfolg eines SQL-Statements von der physischen Speicherreihenfolge der Sätze abhängig ist, darf in einem RDBMS nicht vorkommen. Daher bieten einige RDBMS die Möglichkeit der verzögerten Prüfung bei der Löschweitergabe.

Durch ein einziges DELETE-Statement können mehrere Sätze evtl. auch alle Sätze einer Tabelle gelöscht werden. Innerhalb einer Transaktion können mehrere DELETE-Statements ausgeführt werden. Standardmäßig wird die Prüfung, ob eine Lösch-Operation ausgeführt werden darf, ausgeführt, nach jedem einzelnen Satz, der gelöscht wurde. Das hat den Vorteil, dass bei einer unzulässigen Löschung gleich abgebrochen werden kann und der Rollback nicht unnötig viel zu tun hat.

Um die oben beschriebenen Lösch-Anomalien zu vermeiden, bieten einige RDBMS die Möglichkeit, die Prüfung, ob die Löschung zulässig ist, nicht nach jedem einzelnen Satz auszuführen, sondern

• nach der Löschung aller Sätze, die durch ein DELETE-Statement angewiesen werden (Diese Variante wird z. B. von DB2 angeboten durch die Option ON DELETE NO ACTION)
• erst zum Abschluss der Transaktion auszuführen. (Diese Variante wird z. B. von ORACLE angeboten durch die Option INITIALLY IMMEDIATE DEFERRABLE)
• einige RDBMS bieten auch die Möglichkeit, Fremdschlüssel-Beziehungen zu deaktivieren und später wieder zu aktivieren. Bei einer Aktivierung muss der gesamte Datenbestand der betroffenen Tabelle überprüft werden und es müssen Anweisungen erteilt werden, wie mit fehlerhaften Sätzen umgegangen werden soll.
• Mit Tools (z. B. Import, Load) kann man bei den meisten RDBMS Sätze in eine Tabelle laden ohne dabei die Fremdschlüssel-Beziehungen zu prüfen. Bei DB2 z. B. ist die Tabelle danach gesperrt und muss durch das CHECK-Tool geprüft werden. Erst dann steht die Tabelle wieder für reguläre Zugriffe zur Verfügung.
• wieder andere RDBMS lassen rekursive Fremdschlüsselbeziehungen erst gar nicht zu.

Nachteile

Die Vorteile der referentiellen Integrität haben aber auch ihren Preis, denn jede Prüfung, die von einem RDBMS vorgenommen wird, kostet CPU-Zeit.

Wenn in einer Parent-Tabelle ein Satz gelöscht wird, dann muss das RDBMS alle Child-Tabellen durchsuchen, und überprüfen, ob dieser Schlüssel noch irgendwo verwendet wird. Wenn die Child-Tabelle für die Spalte mit dem Fremdschlüssel keinen Index hat, dann müssen alle Sätze der Child-Tabelle durchsucht werden. Bei einem großen Datenvolumen ist dafür viel Zeit erforderlich (kann mehrere Minuten dauern).

Wenn in einer Child-Tabelle ein Satz eingefügt wird oder wenn ein Fremdschlüssel-Attribut geändert wird, dann muss das RDBMS auf die Parent-Tabelle zugreifen. Da bei den meisten Datenbank-Systemen für einen Primärschlüssel zwingend auch ein entsprechender Index angelegt werden muss, ist hier das Risiko eines langsamen Zugriffs nicht in gleicher Weise gegeben. Doch auch ein Index-Zugriff erfordert seine CPU-Zeit.

In der Praxis stellt sich oft die Aufgabe, große Datenmengen z. B. täglich aus einem System in ein anderes zu importieren. Wenn in dem Liefer-System die Konsistenz der Daten durch referentielle Integrität gesichert ist, dann kann man in dem aufnehmenden System auf die Definition von Fremdschlüsseln verzichten.

Siehe auch

• Schlüssel (Informatik)
• Schlüssel (Datenbank)
• Integritätsbedingung
• Constraint
• Datenintegrität

Weblinks

 Wikibooks: Einfüge- und Lösch-Reihenfolge bestimmen – Lern- und Lehrmaterialien

• Referentielle Integrität – Referentielle Integrität (zusammengefasst)