Das Kefk Network Wiki befindet sich im Testbetrieb.
Referenzielle Integrität
Aus Kefk.
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 |
Definition
Die referentielle Integrität befasst sich mit der Korrektheit zwischen Attributen von Relationen und der Erhaltung der Eindeutigkeit ihrer Schlüssel.
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. Die Option ON UPDATE CASCADE z.B. wird von den meisten RDBMS nicht angeboten.
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.
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
Fremdschlüssel aus mehreren Attributen
Primärschlüssel und Frendschlü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 on delete cascade ) ; insert into p values (1, 1); insert into p values (1, 2); insert into p values (2, 1); insert into p values (2, 2); insert into d values (1, 1, 1); insert into d values (2, 1, 1); insert into d values (3, 1, 2);
Wenn die Fremdschlüssel-Spalten auch Null-Werte enthalten dürfen, dann findet keine Fremdschlüssel-Prüfung statt, sobald der Fremdschlüssel Null-Werte enthält.
insert into d values (4, null, null); insert into d values (5, 1, null); insert into d values (6, null, 1);
Das gilt auch dann, wenn nur eine der Fremdschlüssel-Spalten einen Null-Wert enthält. In diesem Fall kann es passieren, dass die anderen Spalten des Fremdschlüssels Werte erhalten, die es in der Parent-Table gar nicht gibt. Das angegebene Beispiel liefert bei Oracle 10g und DB2 V9 keinen Fehler.
insert into d values (7, null, 99); insert into d values (8, 99, null);
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.
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)
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 (für DB2)
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 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.
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.
Siehe auch
 | Dieses Dokument entstammt in seiner ersten oder einer späteren Version der deutschsprachigen Wikipedia. Es ist dort zu finden unter dem Stichwort Referenzielle_Integrit%C3%A4t, die Liste der bisherigen Autoren befindet sich in der Versionsliste; die Originalfassung kann dort auch bearbeitet werden. Alle Texte der Wikipedia und ihre Derivate stehen unter der GNU-Lizenz für freie Dokumentation. |
