Fremdschlüssel-Beziehungen

In diesem Kapitel werden wir die Verknüpfungen zwischen Tabellen über Fremdschlüssel behandeln. Bereits in den einleitenden Kapiteln wurde die „referentielle Integrität“ betont, dass nämlich die Datensätze in verschiedenen Tabellen dauerhaft zueinander passen müssen. Diesem Zweck dienen die Fremdschlüssel.

(Einführung in SQL: Fremdschlüssel-Beziehungen)

Wikipedia hat einen Artikel zum Thema:

Referentielle Integrität

Wikipedia hat einen Artikel zum Thema:

Fremdschlüssel

Problemstellung

In der Beispieldatenbank gibt es viele Verweise von einer Tabelle auf andere Tabellen:

Zu jedem Eintrag der Tabelle Versicherungsvertrag gehört genau ein Eintrag der Tabelle Fahrzeug: Verträge und Fahrzeuge gehören unmittelbar zusammen.
Zu jedem Eintrag der Tabelle Versicherungsvertrag gehört ein Eintrag der Tabelle Versicherungsnehmer: Ein Vertrag ohne einen Kunden ist sinnlos.
Einem Eintrag der Tabelle Versicherungsnehmer sind Einträge der Tabelle Versicherungsvertrag zugeordnet: Ein Kunde kann einen oder mehrere Verträge halten; zu ihm können auch null Verträge gehören, wenn er nämlich derzeit keinen Vertrag hat, aber als potenzieller Kunde weiterhin registriert ist.
Zu jedem Vertrag gehört ein Eintrag der Tabelle Mitarbeiter: Jeder Vertrag wird hauptsächlich von einem bestimmten Mitarbeiter bearbeitet. Das ist zwar in der Praxis nicht so eng zu sehen, wird aber in unserer theoretischen Firma so geregelt.
Umgekehrt gehört nicht zu jedem Eintrag der Tabelle Mitarbeiter ein Vertrag – z. B. bei der Abteilung „Forschung und Entwicklung“.

Es wäre viel Aufwand, wenn ein Anwender alle diese Bedingungen immer selbständig beachten müsste. In einem Büro kann man immer einmal gestört werden; und schon fehlt eine der unbedingt erforderlichen Informationen: Ein neuer Vertrag wird gespeichert, aber der Kunde fehlt noch; dann kommt ein Telefonat dazwischen; dann macht die Mitarbeiterin mit dem nächsten Vertrag weiter. Und wohin soll die Rechnung zum vorigen Vertrag gehen?

Viel sinnvoller ist es, wenn das DBMS in die Lage versetzt wird, diese Bedingungen direkt zu berücksichtigen. Dies wird über die Fremdschlüssel – englisch: ForeignKeys (FK) – geregelt.

Grundsätze der Lösung

Beziehungen beschreiben

Bei all diesen Beziehungen geht es um die referentielle Integrität, dass nämlich die Verbindungen zwischen den Tabellen und Querverweise immer auf dem aktuellen Stand sind und die Werte in allen Tabellen korrekt zusammenpassen. Weiter unten wird es als „interne Datensicherheit“ bezeichnet: Die Daten sollen innerhalb der Datenbank insofern sicher sein, dass keine Widersprüche zwischen den Daten in verschiedenen Tabellen auftreten.

Durch die Fremdschlüssel werden Beziehungen zwischen Datensätzen verschiedener Tabellen definiert. Das DBMS sorgt dann für die richtigen Verknüpfungen:

Kein neuer Vertrag kann ohne Fahrzeug-ID, Versicherungsnehmer-ID und Mitarbeiter-ID eingetragen werden.
Die entsprechenden Datensätze in den Tabellen, auf die verwiesen wird, müssen zuerst gespeichert werden, bevor ein Vertrag neu aufgenommen werden kann.
Kein Versicherungsnehmer kann gelöscht werden, solange noch Verträge vorliegen.
Bei entsprechender Festlegung können zunächst alle Verträge eines Kunden und sofort der Kundensatz selbst gelöscht werden.

Ergänzend sind weitere Bedingungen denkbar:

Wenn Kundennummern geändert werden, müssen die abhängigen Verträge ebenso geändert werden (Update-Weitergabe); oder Änderungen an Kundennummern werden erst gar nicht zugelassen (Update-Restriktion).

Die letzten Bedingungen können vernachlässigt werden, da die Beziehungen über die IDs geregelt werden. Nach den Regeln der relationalen Datenbanken hat die ID keine andere Bedeutung als die Identifizierung der Datensätze; es gibt niemals die Notwendigkeit, sie zu ändern.

Beziehungen definieren

In der Tabellenstruktur der Beispieldatenbank sind bei den einzelnen Tabellen in der Spalte Erläuterung die Verknüpfungen aufgeführt, beispielsweise:

Die Tabelle Versicherungsvertrag enthält die folgenden Verweise:
- Ein Eintrag in der Spalte Versicherungsnehmer-ID verweist auf eine ID der Tabelle Versicherungsnehmer.
- Die Spalte Fahrzeug-ID verweist auf eine ID der Tabelle Fahrzeug.
- Die Spalte Mitarbeiter-ID verweist auf eine ID der Tabelle Mitarbeiter.
Die Tabelle Zuordnung_SF_FZ enthält die folgenden Verweise:
- Die Spalte Schadensfall-ID verweist auf eine ID der Tabelle Schadensfall.
- Die Spalte Fahrzeug-ID verweist auf eine ID der Tabelle Fahrzeug.
Die Tabelle Versicherungsnehmer enthält den folgenden Verweis:
- Die Spalte Versicherungsgesellschaft-ID verweist optional (nämlich nur bei Fremdkunden) auf eine ID der Tabelle Versicherungsgesellschaft.

In allen Fällen bedeuten die Verweise: In einem Datensatz der jeweils ersten Tabelle stehen keine weiteren Einzelheiten (z. B. zum Versicherungsnehmer). Stattdessen steht dort eine ID; die Angaben dazu sind im Datensatz der „weiteren“ Tabelle unter der genannten ID zu finden. Soweit es sich um einen optionalen Verweis handelt, kann in der „ersten“ Tabelle auch der NULL-Wert stehen; ob ein NULL-Verweis mit einem Fremdschlüssel automatisiert gesteuert werden kann, hängt vom DBMS ab.

Syntax und Optionen

Es sind also zwei Punkte sicherzustellen:

Als Fremdschlüssel in der einen Tabelle (z. B. beim Versicherungsvertrag) dürfen nur solche Werte stehen, die als Primärschlüssel in der anderen Tabelle (z. B. beim Versicherungsnehmer) vorhanden sind.
Ein Primärschlüssel darf nur dann geändert oder gelöscht werden, wenn er nicht als Fremdschlüssel in einer weiteren Tabelle verwendet wird.

Begriffe

Auf der Ebene von Tabellen werden die folgenden Begriffe verwendet; diese beziehen sich immer auf eine bestimmte Fremdschlüssel-Beziehung.

Die Tabelle, auf deren Primärschlüssel verwiesen wird, heißt Primärtabelle; auch die Begriffe Master-Tabelle oder Parent-Tabelle sind gebräuchlich – manchmal auch die deutsche Bezeichnung Eltern-Tabelle.
Im ersten Beispiel der Auflistung sind dies die Tabellen Versicherungsnehmer, Fahrzeug und Mitarbeiter.
Die Tabelle, die den bzw. die Fremdschlüssel enthält, bezeichnet man als Detailtabelle oder Child-Tabelle oder auch abhängige Tabelle – manchmal auch die deutsche Bezeichnung Kind-Tabelle.
Im Beispiel ist das die Tabelle Versicherungsvertrag.

Der Begriff Detailtabelle hat in diesem Beispiel nichts damit zu tun, dass in der Tabelle Einzelheiten zum Fahrzeug oder zum Sachbearbeiter stünden (das ist gerade nicht der Fall). Mit dem Beispiel im Wikipedia-Artikel „Fremdschlüssel“ wird die Bedeutung von Master und Detail verständlich: Die Kunden sind die maßgebende Tabelle; zu jedem Kunden gehören als Details „seine“ Bestellungen.

Wir sprechen deshalb von Detailtabelle mit Fremdschlüsseln: Eine Spalte in der Detail-Tabelle wird über den Fremdschlüssel verknüpft mit dem Primärschlüssel in der Primärtabelle.

Die Syntax

Die Fremdschlüssel gehören zur Definition der Tabellen, ihre Definition über FOREIGN KEY ist also Bestandteil der Data Definition Language (DDL). Wie bei anderen Erweiterungen der Tabellendefinition gibt es mehrere Wege:

Bei der Definition einer einzelnen Spalte, die den Fremdschlüssel benutzt:

    <Definition der Spalte>
    --  ergänzt durch:
        REFERENCES <Primärtabelle> ( <Spaltenname> )
      [ <Optionen> ]

Bei der Definition der Tabelle in der Liste der Einschränkungen (CONSTRAINTS):

      [ CONSTRAINT <Constraint-Name> ] 
        FOREIGN KEY ( <Spaltenname> )
        REFERENCES <Primärtabelle> ( <Spaltenname> )
      [ <Optionen> ]

Als Änderung der Tabellendefinition mit ALTER TABLE:

ALTER TABLE <Detailtabelle>
  ADD [ CONSTRAINT <Constraint-Name> ] 
        FOREIGN KEY ( <Spaltenname> )
        REFERENCES <Primärtabelle> ( <Spaltenname> )
      [ <Optionen> ]

Zu dem Zeitpunkt, an dem ein FOREIGN KEY festgelegt wird, muss die Tabelle, auf die verwiesen wird, bereits definiert sein. Der letzte Weg ist deshalb in der Regel am sichersten: zuerst werden alle Tabellen bestimmt, danach alle FOREIGN KEYs als Verknüpfung.

Zur eigentlichen Definition gehören die folgenden Bestandteile:

CONSTRAINT <Name>
Dies legt den Namen dieser Einschränkung fest und kann auch entfallen.
FOREIGN KEY <Spaltenname>
Dies ist der Hinweis auf einen Fremdschlüssel und bestimmt, zu welcher Spalte dieser gehört.
Bei der Definition einer einzelnen Spalte (erste Variante) ist von vornherein klar, um welche Spalte es sich handelt; dies muss deshalb nicht wiederholt werden und entfällt bei dieser Variante.
REFERENCES <Tabellenname>
Dies bestimmt die Tabelle, auf die mit dem Fremdschlüssel verwiesen wird.
<Spaltenname> in Klammern gesetzt
Damit wird festgelegt, welche Spalten in den beiden Tabellen miteinander in Beziehung gesetzt werden.

Die Optionen werden im nächsten Abschnitt erläutert.

Der Vollständigkeit halber sei darauf hingewiesen: Eine solche Verknüpfung kann sich auch auf mehrere Spalten beziehen, nämlich sowohl als Primärschlüssel als auch als Fremdschlüssel. Da ein Primärschlüssel keine weitere Bedeutung haben soll, besteht er sowieso (fast) immer aus einer einzelnen Spalte; deshalb wird in der Übersicht nur ein <Spaltenname> erwähnt.

Optionen

Die Optionen eines FOREIGN KEY bestimmen das Verhalten der Tabelle, die die Verweise (Fremdschlüssel) benutzt – also der Detailtabelle –, sobald in der Primärtabelle die Primärschlüssel geändert werden. Allgemein steht folgendes Verhalten zur Auswahl:

NO ACTION – alle Änderungen werden verweigert
CASCADE – die Weitergabe der Änderung an die Detailtabelle
RESTRICT – die Verweigerung der Änderung auch in der Primärtabelle
SET NULL – die Änderung des Verweises in der Detailtabelle auf NULL
SET DEFAULT – die Änderung des Verweises in der Detailtabelle auf den Vorgabewert der Spalte

Die verschiedenen Update- und Lösch-Optionen werden nicht von allen DBMS unterstützt. Die Option ON UPDATE CASCADE z. B. wird von den meisten DBMS nicht angeboten.

Im einzelnen wirken sich diese Optionen wie folgt aus.

Bei Neuaufnahmen in der Primärtabelle sind die Datensätze in der übergeordneten Tabelle noch nicht vorhanden. Also kann es keine Probleme geben; deshalb muss dies bei den Optionen nicht beachtet werden.

Die folgenden Optionen wirken sich bei Änderungen und Löschungen in der Primärtabelle in gleicher Weise aus:

ON UPDATE NO ACTION und ON DELETE NO ACTION
Die „Inaktivität“ bedeutet: Wenn ein Primärschlüssel in der Primärtabelle geändert bzw. gelöscht werden soll und abhängige Sätze in der Detailtabelle existieren, dann wird die Änderung/Löschung mit einem Fehler abgebrochen; es erfolgt ein ROLLBACK.
ON UPDATE RESTRICT und ON DELETE RESTRICT
Die „Restriktion der Aktualisierung“ bedeutet: Wenn ein Primärschlüssel in der Primärtabelle geändert bzw. gelöscht werden soll und abhängige Sätze in der Detailtabelle existieren, dann wird die Änderung/Löschung verweigert.
ON UPDATE SET NULL und ON DELETE SET NULL
Das „NULL-Setzen“ bedeutet: Wenn ein Primärschlüssel in der Primärtabelle geändert bzw. gelöscht wird, dann werden die Verweise in der Detailtabelle auf NULL gesetzt. Das ist nur möglich, wenn die betreffende Spalte NULL-Werte zulässt (also nicht mit NOT NULL definiert wurde).
ON UPDATE SET DEFAULT und ON DELETE SET DEFAULT
Das „DEFAULT-Setzen“ bedeutet: Wenn ein Primärschlüssel in der Primärtabelle geändert bzw. gelöscht wird, dann werden die Verweise in der Detailtabelle auf den Vorgabewert der betreffenden Spalte gesetzt. Das ist nur möglich, wenn für die betreffende Spalte ein Vorgabewert festgelegt ist.

Die folgende Option wirkt sich bei Änderungen und Löschungen unterschiedlich aus:

ON UPDATE CASCADE also bei Änderungen
Mit der „Weitergabe der Aktualisierung“ werden die Fremdschlüssel in der Detailtabelle in der gleichen Weise geändert, wie der Primärschlüssel in der Primärtabelle geändert wird.
ON DELETE CASCADE also bei Löschungen
Die „Löschweitergabe“ bedeutet: Zusammen mit dem Datensatz in der Primärtabelle werden auch alle Datensätze in der Detailtabelle gelöscht, die sich auf diesen Schlüssel beziehen.

Wenn der Primärschlüssel „richtig“ definiert ist, nämlich für alle Zeiten unveränderlich ist, dann wäre die UPDATE-Option eigentlich überflüssig. Aber man sollte vorbereitet sein, falls man doch auf die Idee kommt, einen Primary Key zu ändern.

Auswirkungen

Änderungen in der Primärtabelle haben mit den Optionen nichts zu tun. Sie werden durch einen direkten Befehl – INSERT, UPDATE, DELETE – ausgelöst. Ein Fremdschlüssel steuert mit den Optionen nur, inwieweit eine solche Änderung Auswirkungen auf die Detailtabelle hat oder nicht. Allerdings kann es passieren, dass durch die Restriktion nicht nur die Änderung in der Detailtabelle, sondern auch die Änderung in der Primärtabelle verhindert wird.

Änderungen in der Detailtabelle werden durch einen FK wie folgt eingeschränkt: Bei INSERT und UPDATE dürfen in den Spalten des Fremdschlüssels nur solche Werte eingefügt werden, die in der Primärtabelle als Primärschlüssel vorhanden sind. Einzige Ausnahme ist, wenn die Fremdschlüssel-Spalte als optional definiert ist. Dann kann hier auch NULL eingefügt werden, obwohl NULL niemals als Primärschlüssel in der Primärtabelle stehen wird.

Beispiele

Versicherungsvertrag und Kunden

Beginnen wir für die Tabelle Versicherungsvertrag mit dem Verweis von der Spalte Versicherungsnehmer_ID auf die Tabelle Versicherungsnehmer.

ALTER TABLE Versicherungsvertrag 
  ADD CONSTRAINT   Versicherungsvertrag_VN 
      FOREIGN KEY (Versicherungsnehmer_ID) 
      REFERENCES Versicherungsnehmer (ID);

Wie fast immer benutzen wir eine Einschränkung mit Namen. Wie bei den anderen Constraints hängen wir an den Namen der Tabelle „etwas“ an (Suffix), das für die Art der Einschränkung steht. Wenn eine Tabelle nur einen Fremdschlüssel bekommt, wäre FK als Suffix geeignet. Da zur Tabelle Versicherungsvertrag drei Fremdschlüssel gehören, verwenden wir stattdessen den jeweiligen Tabellen-Alias.

Mit diesem CONSTRAINT ist festgelegt: Ein neuer Versicherungsvertrag kann nur dann registriert werden, wenn die dort vorgesehene Versicherungsnehmer_ID in der Tabelle Versicherungsnehmer als ID bereits registriert ist.

Wie gesagt: Eine „richtige“ ID wird niemals mehr geändert. Vorsichtshalber legen wir aber auch die Optionen fest:

ALTER TABLE Versicherungsvertrag 
  ADD CONSTRAINT   Versicherungsvertrag_VN 
      FOREIGN KEY (Versicherungsnehmer_ID) 
      REFERENCES Versicherungsnehmer (ID)
      ON UPDATE RESTRICT
      ON DELETE RESTRICT;

Die Änderung der ID oder die Löschung eines Versicherungsnehmers ist also nicht zulässig, wenn zu diesem Kunden ein Versicherungsvertrag registriert ist.

Mitarbeiter und Abteilung

Die Beziehung zwischen diesen Tabellen kann so festgelegt werden:

ALTER TABLE Mitarbeiter          
  ADD CONSTRAINT Mitarbeiter_FK 
      FOREIGN KEY (Abteilung_ID)           
      REFERENCES Abteilung (ID)
      ON UPDATE CASCADE
      ON DELETE RESTRICT;

Das DBMS sorgt damit für die interne Datensicherheit:

Ein neuer Mitarbeiter kann nicht eingetragen werden, ohne dass die Abteilung_ID in der Tabelle Abteilung als ID vorhanden ist.
Wenn die Nummer einer Abteilung geändert wird, wird das automatisch bei allen ihren Mitarbeitern angepasst und ebenfalls geändert.
Wenn eine Abteilung gelöscht (d. h. geschlossen) werden soll, tritt folgende Prüfung ein:
- Durch ON DELETE RESTRICT kann sie nicht gelöscht werden, solange ihr Mitarbeiter zugeordnet sind.
- Aber mit ON DELETE CASCADE würden beim Löschen einer Abteilung automatisch alle dort arbeitenden Mitarbeiter ebenfalls gestrichen.

Kombination von Fremdschlüsseln

Grundsätzlich kann eine Detail-Tabelle gleichzeitig als Master-Tabelle (Primärtabelle) für eine andere Tabelle definiert werden. In unserer Beispieldatenbank betrifft das die mehrfachen Verknüpfungen:

Versicherungsvertrag  ⇔  Versicherungsnehmer  ⇔  Versicherungsgesellschaft
                      ⇔  Fahrzeug             ⇔  Fahrzeugtyp
                                               ⇔  Fahrzeughersteller
                      ⇔  Mitarbeiter          ⇔  Abteilung
Fahrzeug              ⇔  Zuordnung_SF_FZ      ⇔  Schadensfall

Dann sind aber nicht alle Kombinationen der Optionen CASCADE (Weitergabe) und RESTRICT (Restriktion) zulässig – weder bei DELETE noch bei UPDATE. Das DBMS prüft beim Ausführen der DDL-Befehle, ob die gewünschte Regel zulässig ist oder nicht.

Nicht zulässig sind die folgende Verknüpfungen:

 Tabelle C                Tabelle B                Tabelle A
(Details zu Tabelle B)   (Details zu Tabelle A,   (Master zu Tabelle B)
                         (Master  zu Tabelle C)                            
Versicherungsvertrag  ⇔  Versicherungsnehmer  ⇔  Versicherungsgesellschaft
                ON DELETE RESTRICT       ON DELETE CASCADE

Erläuterung: Es ist nicht zulässig, einen Versicherungsnehmer zu löschen, wenn zu ihm noch (mindestens) ein Vertrag registriert ist. Andererseits sollen mit einer Versicherungsgesellschaft auch alle ihre Versicherungsnehmer automatisch gelöscht werden. Diese automatische Löschung stünde im Widerspruch zur Verhinderung der Löschung bei vorhandenen Verträgen.

Zulässig sind die folgende Verknüpfungen:

 Tabelle C                Tabelle B                Tabelle A
(Details zu Tabelle B)   (Details zu Tabelle A,   (Master zu Tabelle B)
                         (Master  zu Tabelle C)                            
Versicherungsvertrag  ⇔  Versicherungsnehmer  ⇔  Versicherungsgesellschaft
                ON DELETE CASCADE        ON DELETE RESTRICT

Erläuterung: Das Löschen der Versicherungsgesellschaft ist nicht zulässig, wenn noch Versicherungsnehmer registriert sind. Damit wird die Weitergabe oder Restriktion der Löschung vom Versicherungsnehmer zum Versicherungsvertrag überhaupt nicht beeinflusst.

Möglich sind auch Ring-Verkettungen:

Tabelle A  ⇔  Tabelle B  ⇔  Tabelle C  ⇔  Tabelle A

Ob Sie diese Verknüpfungen von rechts nach links lesen oder umgekehrt, ist gleichgültig: Eine Tabelle hat Verweise auf eine andere, diese auf eine nächste und eine weitere wieder auf die erste.

Rekursive Fremdschlüssel

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

Als Beispiel verwenden wir eine hierarchische Gliederung der Abteilungen:

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

Die gesamte Firma wird mit AbtNr = 1 gespeichert; zu ihr gehört keine UebergeordneteAbt. Bei jeder anderen Abteilung wird in dieser Spalte die AbtNr derjenigen Abteilung registriert, der sie direkt zugeordnet ist: Eine Hauptabteilung gehört direkt zur Firma, eine beliebige Abteilung zu ihrer Hauptabteilung, eine Arbeitsgruppe zu einer bestimmten Abteilung usw.

Praktische Probleme

Rekursive Fremdschlüsselbeziehungen sind etwas problematisch in der Handhabung.

Die Neuaufnahme von Datensätzen muss in einer bestimmten Reihenfolge geschehen: zuerst die oberste Ebene (Firma), dann die nächste Ebene (Hauptabteilungen) usw. Auf jeder Ebene ist eine Neuaufnahme nur dann möglich, wenn der übergeordnete Eintrag schon vorhanden ist.

Beim Löschen von Datensätzen kann es zu verschiedenen Problemen kommen.

Mit Lösch-Weitergabe – ON DELETE CASCADE – könnten wesentlich mehr Daten gelöscht werden, als in der WHERE-Bedingung angegeben wird:

DELETE FROM Abteilung
 WHERE AbtNr = 33;

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

Mit Lösch-Restriktion – ON DELETE RESTRICT – wird nach jeder einzelnen Löschung 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-Befehls die Sätze in einer beliebigen Reihenfolge gelöscht werden, meistens in der Reihenfolge, in der sie „real“ in der Tabelle 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-Befehls von der physischen Speicherreihenfolge der Sätze abhängig ist, darf in einem DBMS nicht vorkommen. Daher bieten einige DBMS die Möglichkeit der verzögerten Prüfung bei der Löschweitergabe.

Durch ein einziges DELETE-Statement können mehrere Sätze und unter Umständen auch alle Sätze einer Tabelle gelöscht werden. Innerhalb einer Transaktion können mehrere DELETE-Statements ausgeführt werden. Standardmäßig erfolgt die Prüfung, ob eine Lösch-Operation ausgeführt werden darf, 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.

Maßnahmen

Um die oben beschriebenen Lösch-Anomalien zu vermeiden, kann bei einigen DBMS die Prüfung, ob die Löschung zulässig ist, als Gesamtprüfung stattfinden und nicht nach jedem einzelnen Satz:

Nach der Löschung aller Sätze, die durch ein DELETE-Statement angewiesen werden, wird der Zusammenhang der Daten überprüft.
Diese Variante wird z. B. von DB2 angeboten durch die Option ON DELETE NO ACTION.
Erst zum Abschluss der Transaktion wird die Prüfung erledigt.
Diese Variante gibt es z. B. bei Oracle durch die Option INITIALLY IMMEDIATE DEFERRABLE.
Einige DBMS können Fremdschlüssel-Beziehungen deaktivieren und später wieder 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 DBMS 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 DBMS lassen rekursive Fremdschlüsselbeziehungen erst gar nicht zu.

Reihenfolge der Maßnahmen beachten

Wenn die Tabellen in einer Datenbank mit Fremdschlüsseln verbunden sind, dann muss beim Bearbeiten der Tabellen eine bestimmte Reihenfolge eingehalten werden, und zwar sowohl beim Einfügen als auch beim Löschen. Ob auch das Ändern mit Schwierigkeiten verbunden sein kann, hängt von der Situation ab.

Alle hier genannten Probleme beziehen sich ausschließlich auf diejenigen Spalten, die mit Fremdschlüsseln an andere Tabellen gebunden sind. Änderungen in anderen Spalten können immer ohne Probleme ausgeführt werden.

Bei INSERT

Man muss mit den Tabellen beginnen, die keine Fremdschlüssel haben. Danach können die Tabellen befüllt werden, die auf diese Tabellen verweisen, und so weiter. Bei unserer Beispieldatenbank bedeutet das für einen neuen Versicherungsvertrag:

Registriere den Versicherungsnehmer.
- Dazu ist vorher ggf. die Versicherungsgesellschaft zu speichern.
Registriere das Fahrzeug.
- Dazu ist vorher ggf. der Fahrzeugtyp zu speichern
- und noch einen Schritt früher der Fahrzeughersteller.
Registriere, soweit notwendig, den Mitarbeiter.
- Dazu ist vorher ggf. die Abteilung zu speichern.

Erst jetzt sind alle Voraussetzungen vorhanden, sodass der Vertrag gespeichert werden kann.

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 DELETE

Zum Löschen von Datensätzen muss – sofern nicht mit einer automatischen Lösch-Weitergabe gearbeitet wird – genau die umgekehrte Reihenfolge eingehalten werden.

Bei UPDATE

Die Fremdschlüssel-Beziehungen verlangen, dass die Datensätze in der Primärtabelle, auf die aus der Detailtabelle verwiesen wird, vorhanden sind. Ändert man einen Fremdschlüssel in der Detailtabelle, muss der neue Wert ebenfalls in der Primärtabelle vorhanden sein. Wenn man einen Schlüsselwert in der Primärtabelle ändern will, dann ist das nur möglich, wenn der alte Wert von keinem Satz in der Detailtabelle verwendet wird. Sollte der Wert doch verwendet (referenziert) werden, dann ist der UPDATE nicht möglich.

Theoretisch wäre auch ein UPDATE-CASCADE vorstellbar. Das würde bedeuten, dass die Änderung eines Wertes in der Primärtabelle auch gleichzeitig alle referenzierten Werte in der Detailtabelle mitändert. Eine solche Funktion wird jedoch von den meisten Datenbanken nicht angeboten.

Wie geht man also vor, wenn man einen Schlüsselwert in der Primärtabelle ändern will, der von mehreren Sätzen in der Detailtabelle verwendet wird? Diese Aufgabe wird in drei Schritten gelöst:

Einen Satz mit dem neuen Wert in der Primärtabelle einfügen (INSERT)
Alle Sätze in der Detailtabelle ändern auf den neuen Wert (UPDATE)
Den Satz mit dem alten Wert in der Primärtabelle löschen (DELETE)

Bestimme die „Einfüge-Reihenfolge“

Theoretisch handelt es sich hier um ein Problem der „topologischen Sortierung“.

Bei großen Datenmodellen sollte man alle vorhandenen Tabellen in der Reihenfolge notieren, in der sie befüllt werden können. Dazu kann man die Struktur der Datenbank auslesen; Tabellen und Fremdschlüssel müssen also bereits definiert sein. Beispiele dazu sind im Kapitel Tipps und Tricks zu finden.

Zusammenfassung

In diesem Kapitel erfuhren wir einiges darüber, wie mit Fremdschlüsseln (= FOREIGN KEYs) die Verbindungen zwischen Tabellen sichergestellt werden:

Die Definition gehört zur DDL und erfolgt mit einer entsprechenden Einschränkung (CONSTRAINT), meistens über ALTER TABLE.
Sowohl beim Einfügen von Datensätzen als auch beim Ändern und Löschen sind diese Beziehungen zu beachten.
Daraus ergibt sich eine bestimmte Reihenfolge, welche Daten zuerst gespeichert werden müssen.
Beim Ändern und Löschen können die Anpassungen durch eine Klausel automatisiert werden.

Übungen

Bei allen SQL-Befehlen benutzen Sie bitte CONSTRAINTS mit Namen.

Übung 1

Definitionen

Zur Lösung

Welche der folgenden Aussagen sind wahr, welche sind falsch?

Ein Fremdschlüssel legt fest, dass in einer bestimmten Spalte der einen Tabelle nur solche Werte verwendet werden können, die als Primärschlüssel der anderen Tabelle vorhanden sind.
Die Tabelle, die den Primärschlüssel enthält, wird als Master-Tabelle bezeichnet, die Tabelle mit dem Fremdschlüssel als Detail-Tabelle.
In unserer Beispieldatenbank ist bei der Verknüpfung Fahrzeugtyp/Fahrzeughersteller die Tabelle Fahrzeugtyp der „Master“, die Tabelle Fahrzeughersteller die Detailtabelle.
Bei der Verknüpfung Fahrzeug/Fahrzeugtyp gilt die Tabelle Fahrzeug als Detailtabelle, die Tabelle Fahrzeugtyp als Primärtabelle.
Ein INSERT in der Detailtabelle ist immer möglich, ohne die Werte in der Primärtabelle zu beachten.
Ein UPDATE in der Primärtabelle ist immer möglich, ohne die Werte in der Detailtabelle zu beachten.
Ein DELETE in der Detailtabelle ist immer möglich, ohne die Werte in der Primärtabelle zu beachten.
Ein DELETE in der Primärtabelle ist immer möglich, ohne die Werte in der Detailtabelle zu beachten.
Ein FOREIGN KEY wird so definiert: Er wird der Tabelle mit dem Fremdschlüssel und der betreffenden Spalte zugeordnet und verweist auf die Tabelle mit dem Primärschlüssel und der betreffenden Spalte.
Ein FOREIGN KEY kann nicht unmittelbar bei der Definition der Fremdschlüssel-Spalte angegeben werden.
Eine Fremdschlüssel-Beziehung kann nur zu jeweils einer Spalte der beiden Tabellen gehören.

Übung 2

Zusammenhänge

Zur Lösung

Welche Fremdschlüssel sind bei den Tabellen Fahrzeug und Mitarbeiter der Beispieldatenbank vorzusehen? Nennen Sie jeweils die betreffenden Spalten. Sind bei den Primärtabellen weitere Fremdschlüssel vorzusehen?

Übung 3

Fremdschlüssel festlegen

Zur Lösung

Verknüpfen Sie die Tabelle Fahrzeugtyp mit der Tabelle Fahrzeughersteller als Fremdschlüssel auf die entsprechenden Spalten.

Übung 4

Fremdschlüssel festlegen

Zur Lösung

Verknüpfen Sie die Tabelle Dienstwagen mit den Tabellen Mitarbeiter und Fahrzeugtyp als Fremdschlüssel auf die entsprechenden Spalten.

Übung 5

Optionen bei Neuaufnahmen

Zur Lösung

Welche der folgenden Aussagen sind wahr, welche sind falsch?

Für Neuaufnahmen gibt es keine Option ON INSERT zur Automatisierung.
Bei einer Neuaufnahme in der Primärtabelle müssen Verknüpfungen in der Detailtabelle nicht beachtet werden.
Bei einer Neuaufnahme in der Detailtabelle müssen Verknüpfungen in der Primärtabelle nicht beachtet werden.
Bei einer Neuaufnahme in der Primärtabelle kann der erforderliche Datensatz in der Detailtabelle automatisch aufgenommen werden.
Bei einer Neuaufnahme in der Detailtabelle kann der erforderliche Datensatz in der Primärtabelle automatisch aufgenommen werden.
Bei Neuaufnahmen in beiden Tabellen ist die Reihenfolge zu beachten.

Übung 6

Optionen bei Änderungen

Zur Lösung

Welche der folgenden Aussagen sind wahr, welche sind falsch?

Bei einer Änderung in der Primärtabelle müssen Verknüpfungen in der Detailtabelle nicht beachtet werden.
Bei einer Änderung in der Detailtabelle müssen Verknüpfungen in der Primärtabelle nicht beachtet werden.
Bei einer Änderung in der Primärtabelle wird der zugehörige Datensatz in der Detailtabelle automatisch geändert, sofern ON UPDATE SET DEFAULT definiert ist.
Bei einer Änderung in der Primärtabelle wird der zugehörige Datensatz in der Detailtabelle automatisch geändert, sofern ON UPDATE CASCADE festgelegt ist.
Eine Änderung in der Detailtabelle ändert auch den zugehörigen Datensatz in der Primärtabelle, sofern ON UPDATE CASCADE gilt.
Sofern ON UPDATE RESTRICT festgelegt ist, wird eine Änderung in der Primärtabelle immer ausgeführt.

Übung 7

Die Reihenfolge

Zur Lösung

Nennen Sie die Reihenfolge der INSERT-Befehle, wenn ein Schadensfall mit drei beteiligten Fahrzeugen aufzunehmen ist. Dabei soll ein Fahrzeug zu einem „Eigenen Kunden“ und zwei Fahrzeuge zu „Fremdkunden“ gehören; die eine „fremde“ Versicherungsgesellschaft soll schon gespeichert sein, die andere nicht.

Lösungen

Lösung zu Übung 1

Definitionen

Zur Übung

Die Aussagen 1, 2, 4, 7, 9 sind wahr. Die Aussagen 3, 5, 6, 8, 10, 11 sind falsch.

Hinweis zu Aussage 6: Die Ergänzung „immer“ macht die Aussage falsch. Wenn die ID ausgenommen würde, wäre die Aussage wahr.

Lösung zu Übung 2

Zusammenhänge

Zur Übung

Fahrzeug: Fahrzeugtyp_ID verweist auf ID der Tabelle Fahrzeugtyp.
Fahrzeugtyp: Hersteller_ID verweist auf ID der Tabelle Fahrzeughersteller.
Mitarbeiter: Abteilung_ID verweist auf ID der Tabelle Abteilung.

Lösung zu Übung 3

Fremdschlüssel festlegen

Zur Übung

ALTER TABLE Fahrzeugtyp          
  ADD CONSTRAINT Fahrzeugtyp_FK 
      FOREIGN KEY (Hersteller_ID)          
      REFERENCES Fahrzeughersteller (ID);

Lösung zu Übung 4

Fremdschlüssel festlegen

Zur Übung

ALTER TABLE Dienstwagen          
  ADD CONSTRAINT Dienstwagen_FZ
      FOREIGN KEY (Fahrzeugtyp_ID)    REFERENCES Fahrzeugtyp (ID),
  ADD CONSTRAINT Dienstwagen_MI
      FOREIGN KEY (Mitarbeiter_ID)    REFERENCES Mitarbeiter (ID);

Lösung zu Übung 5

Optionen bei Neuaufnahmen

Zur Übung

Die Aussagen 1, 2, 6 sind wahr. Die Aussagen 3, 4, 5 sind falsch.

Lösung zu Übung 6

Optionen bei Änderungen

Zur Übung

Die Aussagen 3, 4 sind wahr. Die Aussagen 1, 2, 5, 6 sind falsch.

Lösung zu Übung 7

Die Reihenfolge

Zur Übung

die „neue“ Versicherungsgesellschaft speichern
deren Kunden speichern
dessen Fahrzeug speichern
dessen Versicherungsvertrag speichern
den Fremdkunden der schon registrierten Versicherungsgesellschaft speichern
dessen Fahrzeug speichern
dessen Versicherungsvertrag speichern
den Schadensfall speichern
den Schadensfall mit den Fahrzeugen verknüpfen:
1. mit dem Fahrzeug des eigenen Kunden
2. mit dem Fahrzeug des einen Fremdkunden
3. mit dem Fahrzeug des anderen Fremdkunden

Siehe auch

Grundlagen und weitere Einzelheiten sind in den folgenden Kapiteln zu finden:

Relationale Datenbanken – Normalisierung
Tabellenstruktur der Beispieldatenbank
Data Definition Language (DDL)
DDL - Einzelheiten mit Einzelheiten zur Tabellendefinition

Wikipedia bietet verschiedene grundlegenden Informationen:

Inhaltsverzeichnis: Erweiterungen

Zurück zu: DDL – Einzelheiten

Weiter zu: Programmierung