7.3.2 Referentielle Integrität

7.3.2 Referentielle Integrität – Fremdschlüssel

Für die Einführung der Fremdschlüssel benötigen wir noch eine zweite Tabelle. Sehen wir uns dazu einen Teil der Mitabeitertabellen-Struktur an. Sie wird auch wieder mit der CREATE TABLE-Systax angelegt.

CREATE TABLE mitarbeiter
  (
    personal_ID CHAR(4) NOT NULL,
    name VARCHAR(30) NOT NULL,
    strasse VARCHAR(50) NOT NULL,
    plz VARCHAR(5) NULL DEFAULT '00000',
    geburtsdatum DATE NULL DEFAULT '0000-00-00',

    CONSTRAINT primaryKey_mitarbeiter PRIMARY KEY (personal_ID)
  ) ENGINE=INNODB

Wie Sie sehen, beinhalten nun sowohl die Tabelle mitarbeiter als auch die Tabelle ort eine Spalte plz. Der Vorteil dieses Szenarios besteht darin, dass es bei einem Umzug des Mitarbeiters ausreicht, die neue Postleitzahl in die Mitarbeitertabelle zu schreiben - Ort und Vorwahl werden dann aus der Tabelle ort automatisch korrekt zugeordnet.

Damit dies funktioniert, benötigen wir die Definition eines Fremdschlüssels.

Ein Fremdschlüssel ist eine Spalte, die als Inhalt die Werte der Primärschlüssel einer anderen Tabelle enthält.

Das übernimmt das Constraint vom Typ FOREIGN KEY (Fremdschlüssel). Dieses Constraint gibt an, dass Werte in einer Spalte bzw. in einer Gruppe von Spalten mit den Werten von irgendeiner Zeile in einer anderen Tabelle übereinstimmen. Der Fremdschlüssel muss dabei auf den Primärschlüssel der anderen Tabelle verweisen.
Wird diese Schlüsselbeziehung ständig auf Korrektheit überwacht so spricht man von referentieller Integrität.

Allgemein ist für das Definieren des Fremdschlüssels folgendes zu schreiben:

CREATE TABLE tabellenName
  (
    Spalte_1 Datentyp NOT NULL,
    Spalte_2 Datentyp,
    ...,

    CONSTRAINT schluesselName PRIMARY KEY (Spalte_1),
    CONSTRAINT fremdschluesselName FOREIGN KEY (Spalte_2)
                  REFERENCES tabellenName_2 (Spalte_1)
  )

Bei der Deklaration des Fremdschlüssels wird diesem ein Name gegeben und in Klammern das Attribut bzw. eine Attributkombination (getrennt durch Kommas) angegeben, die den Fremdschlüssel darstellen - bis dahin also eine ähnliche Notation wie sie bei dem Primärschlüssel-Constraint schon vorgestellt wurde.
Jetzt muss noch festgelegt werden, auf welches Attribut in welcher Tabelle sich der FOREIGN KEY bezieht. In der gezeigten Anweisung taucht das Wort REFERENCES gefolgt von dem Tabellennamen und dem Attribut (Verweis auf den Primärschlüssel!) auf.

Der Fremdschlüssel und der Primärschlüssel müssen vom gleichen Datentyp sein - manche Datenbankmanagementsysteme (DBMS) verlangen sogar identische Feldgröße. Beachten Sie das bei der Planung Ihrer Datenbank.

Schauen wir uns nun unser Beispiel an und stellen eine Verbindung zwischen der Tabelle ort und der Tabelle mitarbeiter über die Postleitzahl plz her. D.h., die mitarbeiter-Tabelle soll mit ihrem Attribut plz auf die Tabelle ort verweisen (REFERENCES).

[diese kann Anweisung getestet werden,
danach Tabelle wieder löschen]
CREATE TABLE mitarbeiter
  (
    personal_ID CHAR(4) NOT NULL,
    name VARCHAR(30) NOT NULL,
    strasse VARCHAR(50) NOT NULL,
    plz VARCHAR(5) NULL DEFAULT '00000',
    geburtsdatum DATE NULL DEFAULT '0000-00-00',

    CONSTRAINT primaryKey_mitarbeiter PRIMARY KEY (personal_ID)
    CONSTRAINT foreignKey_mitarbeiter FOREIGN KEY (plz)
                  REFERENCES ort(plz)
  ) ENGINE=INNODB

SQL wird beim Anlegen der neuen Tabelle mit dem Fremdschlüssel überprüfen, ob es die referenzierte Tabelle gibt und dann erst die Tabelle mitarbeiter erzeugen, ansonsten gibt das Datenbanksystem einen Fehler zurück.

Grundkurs SQL

7.3.2 Referentielle Integrität – Fremdschlüssel