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.
[Bearbeiten] 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
SQL-Quelltext:
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:
SQL-Quelltext:
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.
SQL-Quelltext:
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.
[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.
(DB2 für zOS und DB2 für Linux, Unix, Windows)
SQL-Quelltext:
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
)
-- Ermitteln der Ebenen 1 bis 100
SELECT MAX(level) ebene, tabelle
FROM ebenen
GROUP BY tabelle
-- Hinzufügen der Tabellen aus Cycles mit mehr als einem Element
UNION ALL
SELECT DISTINCT 200 ebene, name tabelle
FROM sysibm.systables
WHERE creator = 'P123'
AND type = 'T'
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 type = 'T'
AND name NOT IN (SELECT parent FROM rel
UNION SELECT child FROM rel)
ORDER BY 1, 2
;
[Bearbeiten] Beispiel für ORACLE
getestet mit Oracle Version 10g
WITH rel AS
(
-- rel start
SELECT
child_c.table_name child
, parent_c.table_name parent
FROM user_constraints child_c, user_constraints parent_c
WHERE child_c.constraint_type = 'R'
AND child_c.r_constraint_name = parent_c.constraint_name
-- rel ende
)
, ebenen AS
(
-- ebenen start
SELECT level+1 ebene, child tabelle
FROM rel
CONNECT BY PRIOR child = parent
START WITH parent IN
(
SELECT parent
FROM rel
WHERE parent NOT IN (SELECT child FROM rel)
)
UNION
-- Ebene 1 hinzufügen
SELECT 1, parent
FROM rel
WHERE parent NOT IN (SELECT child FROM rel)
UNION
-- Ebene 100 für Cycle mit nur einem Element hinzufügen
SELECT 100, parent
FROM rel
WHERE parent = child
-- ebenen ende
)
-- Ermitteln der Ebenen 1 bis 100
SELECT MAX(ebene) ebene, tabelle
FROM ebenen
GROUP BY tabelle
-- Hinzufügen der Tabellen aus Cycles mit mehr als einem Element
UNION ALL
SELECT DISTINCT 200 ebene, table_name tabelle
FROM user_tables
WHERE table_name NOT IN (SELECT tabelle FROM ebenen)
AND table_name IN (SELECT parent FROM rel)
-- Hinzufügen aller anderen Tabellen ohne RI
UNION ALL
SELECT 300 ebene, table_name tabelle
FROM user_tables
WHERE table_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.
Diese Lösung für Oracle ist ziemlich inperformant. Sie funktioniert nur gut, wenn es sich um eine geringe Anzahl von Tabellen handelt. Wenn man es mit vielen von einander abhängigen Tabellen zu tun hat, dann wird die Performens besser, wenn man anstelle der Inline-Views richtige Tabellen definiert und durch geeignete Indices ergänzt.
