Referenzielle Integrität
aus Wikipedia, der freien Enzyklopädie
Die referenzielle Integrität ist eine Form der Datenintegrität. Unter der referenziellen Integrität versteht man die Integrität auf Beziehungsebene. Neben der referenziellen Integrität unterscheidet man noch die Wertebereichsintegrität (Integrität auf Datenfeldebene) und die Datenintegrität auf Datensatzebene (siehe Integritätsbedingung).
Inhaltsverzeichnis |
[Bearbeiten] Definition
Die referentielle Integrität befasst sich mit der Korrektheit zwischen Attributen von Relationen und der Erhaltung der Eindeutigkeit ihrer Schlüssel.
[Bearbeiten] Verwendung in Datenbanksystemen
Die Beziehungen werden zuvor in einem Datenbanksystem festgelegt. Das Datenbanksystem wird dann diese 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 Anomalien 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 Lagertabelle können nur dann neue Waren aufgenommen werden, wenn zuvor der entsprechende Lieferant mit seiner Lieferantennummer (= Primärschlüssel) erfasst wurde. Wird der Lieferant gelöscht, müssen auch die von ihm gelieferten Waren in der Lagertabelle automatisch gelöscht werden, da sonst die Datenbank inkonsistent wird.
Technisch wird die referentielle Integrität zwischen zwei Relationen über einen so genannten Fremdschlüssel definiert. Beide Relationen benötigen ein gemeinsames Attribut, das in der Quellrelation ein Primärschlüssel sein muss. Die zweite Relation verweist („referenziert“) über den Fremdschlüssel auf diesen Primärschlüssel. Die Datenbank stellt sicher, dass der Primärschlüssel existiert und nur gemeinsam mit dem Fremdschlüssel geändert oder gelöscht werden kann.
Auf der Ebene von Tabellen bezeichnet man die Tabelle, auf deren Primärschlüssel verwiesen wird, auch 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 oder Mastertabelle und Detailtabelle verwendet.
Für Aktualisierungen des Primärschlüssels in der Mastertabelle sind folgende Optionen möglich:
- ON UPDATE CASCADE (Aktualisierungsweitergabe) bedeutet, dass die Aktualisierung auch an den Fremdschlüsseln der Detailtabelle vorgenommen wird.
- ON UPDATE RESTRICT (Aktualisierungsrestriktion) bedeutet: Wenn ein Schlüssel in der Mastertabelle geändert werden soll, und es existieren abhängige Sätze in der Detailtabelle, dann wird die Änderung verweigert.
Für Löschungen in der Mastertabelle sind folgende Optionen möglich:
- ON DELETE CASCADE (Löschweitergabe) bedeutet, es werden auch alle Sätze in der Detailtabelle gelöscht, die auf diesen Schlüssel referenzieren.
- ON DELETE RESTRICT (Löschrestriktion) bedeutet: wenn ein Satz in der Mastertabelle gelöscht werden soll, und es existieren abhängige Sätze in der Detailtabelle, dann wird die Löschung verweigert.
- ON DELETE SET NULL (Nullifies) bedeutet: wenn ein Satz in der Mastertabelle gelöscht werden soll, und es existieren abhängige Sätze in der Detailtabelle, 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.
Für die Detailtabelle bedeutet ein Fremdschlüssel die Restriktion, dass in diese Spalten nur Werte eingefügt (INSERT / UPDATE) werden dürfen, die in der Mastertabelle 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 Mastertabelle stehen wird.
[Bearbeiten] Beispiel
Annahme, es wurden zwei Tabellen wie folgt in einer Datenbank angelegt:
|
|
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
[Bearbeiten] 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)
[Bearbeiten] 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.
ist nicht zulässig:
Tabelle A (Mastertabelle)
^
| mit Löschweitergabe
|
Tabelle B (Detailtabelle von A)
^
| mit Lösch-Restriktion
|
Tabelle C (Detailtabelle von B)
ist zulässig:
Tabelle A (Mastertabelle)
^
| mit Lösch-Restriktion
|
Tabelle B (Detailtabelle von A)
^
| mit Löschweitergabe
|
Tabelle C (Detailtabelle von B)
[Bearbeiten] Nachteile
Die Vorteile der Referenziellen 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 Referenzielle Integrität gesichert ist, dann kann man in dem aufnehmenden System auf die Definition von Fremdschlüsseln verzichten.
Ein weiterer Nachteil von Referenzieller Integrität ist das Generieren von Testdaten zur Validierung von Programmen. Wenn die Tabellen untereinander verknüpft sind, dann kann nicht eine Tabelle "mal eben" mit Testdaten befüllt werden. Zuvor müssen alle übergeordneten Tabellen (Parent-Tabellen) mit Testdaten befüllt werden. Möglicherweise haben diese Tabellen selber weitere Fremdschlüssel und so weiter.
[Bearbeiten] Einfüge- und Lösch-Reihenfolge bestimmen
Wenn die Tabellen in einer Datenbank mit Fremdschlüssel-Beziehungen verbunden sind, dann muss zum Befüllen der Tabellen eine bestimmte Reihenfolge eingehalten werden. Man muss mit den Tabellen beginnen, die keine Fremdschlüssel haben. Danach können die zu diesen Tabellen untergeordneten Tabellen befüllt werden und so weiter. Zum Löschen von Datensätzen muss - sofern nicht mit einer automatischen Lösch-Weitergabe gearbeitet wird - genau die umgekehrte Reihenfolge eingehalten werden.
Wenn Ring-Verkettungen vorkommen, dann müssen Tools zum initialen Befüllen verwendet werden oder es muss mit Sätzen begonnen werden, die null als Fremdschlüssel enthalten.
Bei großen Datenmodellen lohnt es sich, sich alle vorhandenen Tabellen in der Reihenfolge zu notieren, in der sie befüllt werden können.
Theoretisch handelt es sich hier um ein Problem der topologischen Sortierung.
SQL-Statement zur Bestimmung der Einfüge-Reihenfolge bei Tabellen, die mit Fremdschlüssel verbunden sind. Voraussetzung ist, dass die Tabellen bereits erstellt sind, denn dann kann man aus dem Datenbank-Katalog die Beziehungen entnehmen.
Beispiel für DB2:
WITH
REL(CHILD, PARENT) AS
(
SELECT
TBNAME CHILD
, REFTBNAME PARENT
FROM SYSIBM.SYSRELS
WHERE CREATOR = 'P123'
AND REFTBCREATOR = 'P123'
) ,
EBENEN ( LEVEL, TABELLE) AS
(
SELECT 1, PARENT
FROM REL
WHERE PARENT NOT IN (SELECT CHILD FROM REL)
UNION ALL
SELECT LEVEL+1, CHILD
FROM REL, EBENEN
WHERE EBENEN.TABELLE = REL.PARENT
AND LEVEL < 100
)
SELECT MAX(LEVEL) EBENE, TABELLE
FROM EBENEN
GROUP BY TABELLE
-- Hinzufügen der Tabellen aus einem Cycle
UNION ALL
SELECT DISTINCT 200 EBENE, NAME TABELLE
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'P123'
AND NAME NOT IN (SELECT TABELLE FROM EBENEN)
AND NAME IN (SELECT PARENT FROM REL)
-- Hinzufügen aller anderen Tabellen ohne RI
UNION ALL
SELECT 300 EBENE, NAME TABELLE
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'P123'
AND NAME NOT IN (SELECT PARENT FROM REL
UNION SELECT CHILD FROM REL)
ORDER BY 1, 2
;
- Wenn man alle Tabellen befüllen will, dann muss man mit der Ebene 1 beginnen. Danach kommt die Ebene 2 dran und so weiter.
- Auf Ebene 100 werden Cycle mit nur einem Element ausgegeben.
- Auf Ebene 200 werden Cycle mit mehr als einem Element ausgegeben.
- Auf Ebene 300 werden alle Tabellen ausgegeben, die überhaupt keine Beziehungen zu anderen Tabellen haben.
