Fremdschlüssel: Verbindungen zwischen Tabellen
Kurz gesagt: REFERENCES macht aus einer Spalte einen Fremdschlüssel, und ON DELETE (CASCADE, SET NULL, RESTRICT) sagt, was mit den Zeilen passiert, die auf eine gelöschte Zeile zeigen.
Problem
Lösch mein Konto
Maria Ivanova schreibt: „Bitte lösche mein Konto.“ Du versuchst es:
Die Datenbank weigert sich. Maria hat 12 Bestellungen, und jede zeigt auf sie. Verschwindet die Kundin, zeigen die Bestellungen auf niemanden.
Was soll mit ihren Bestellungen passieren? Und mit ihren Bewertungen? Und mit ihrer Liste der Lieblingsprodukte? Die Antwort ist für jede Tabelle anders. Und sie steht im Schema.
Die Idee
REFERENCES
Den Fremdschlüssel kennst du schon: eine Spalte, die auf eine Zeile in einer anderen Tabelle zeigt. In CREATE TABLE schreibt man ihn mit REFERENCES Tabelle(Spalte). So sind die Bestellungen des Shops gebaut:
orders
- PK
idinteger - FK
customer_idinteger *→ customers ordered_attext *statustext *
Hier ist eine neue Tabelle: die Lieblingsprodukte jedes Kunden. Die erste Zeile geht hinein, die zweite zeigt auf Kunde 99, den es nicht gibt:
Jetzt bewacht die Datenbank die Verweise selbst. Eine Zeile, die auf einen Kunden oder ein Produkt zeigt, das es nicht gibt, kommt nicht hinein.
Probier es aus
Die Lieblingsprodukte
Die Tabelle wishlist hat ihre Spalten schon. Lass customer_id auf customers zeigen und product_id auf products.
Lösung
CREATE TABLE wishlist (
customer_id INTEGER NOT NULL REFERENCES customers(id),
product_id INTEGER NOT NULL REFERENCES products(id),
PRIMARY KEY (customer_id, product_id)
);customer_id INTEGER NOT NULL REFERENCES customers(id) und product_id INTEGER NOT NULL REFERENCES products(id). Schreibst du nach dem Tabellennamen keine Spalte, nimmt SQLite an, dass du auf den Primärschlüssel zeigst.
Die Idee
ON DELETE CASCADE
Nach REFERENCES kannst du sagen, was passieren soll, wenn die Zeile gelöscht wird, auf die du zeigst. Das ist ON DELETE.
CASCADE heißt „lösch mich auch“. Yavor Penev (Kunde 20) hat zwei Lieblingsprodukte. Teodora Genova (Kunde 19) hat eins. Yavor löscht sein Konto:
Yavors Favoriten sind mit ihm verschwunden. Teodoras sind noch da. Eine Liste der Favoriten ergibt ohne den Kunden keinen Sinn, deshalb ist CASCADE hier richtig.
Die Idee
SET NULL und RESTRICT
SET NULL heißt „behalte mich, aber ohne den Verweis“. Fragen zu Produkten sind auch für andere Käufer nützlich, selbst wenn der Autor weg ist:
Die Frage ist geblieben, und customer_id wurde NULL: eine Frage eines früheren Kunden.
RESTRICT heißt „erlaube das Löschen nicht“. Rechnungen müssen laut Gesetz jahrelang aufbewahrt werden:
Ohne ON DELETE ist das Verhalten fast dasselbe: Das Löschen bricht ab. Deshalb konnte Maria ihr Konto nicht löschen. Die Bestellungen sind die Geschichte des Shops und dürfen nicht verschwinden.
Genauer, für Neugierige
Ohne ON DELETE gilt die Regel NO ACTION. Der einzige Unterschied zu RESTRICT ist, wann geprüft wird: RESTRICT bricht sofort ab, NO ACTION prüft am Ende der Anweisung (oder der Transaktion, wenn die Prüfung aufgeschoben ist). Es gibt auch ON UPDATE für das Ändern des Schlüssels, das aber selten gebraucht wird, weil sich ein Primärschlüssel fast nie ändert. Auch ON DELETE SET DEFAULT ist selten.
Sieh es dir an
SET NULL gegen NOT NULL
Jemand hat die Tabelle der Fragen so geschrieben:
customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE SET NULL
Yavor löscht sein Konto noch einmal:
SET NULL will NULL speichern, und NOT NULL erlaubt es nicht. Das Löschen bricht ab. SQLite warnt dich bei CREATE TABLE nicht davor, sondern erst, wenn jemand zu löschen versucht. Eine Spalte mit SET NULL muss leer sein dürfen.
Probier es aus
Die Lieferadressen
Die Tabelle addresses enthält die Adressen der Kunden. Wenn ein Kunde sein Konto löscht, müssen seine Adressen mit ihm verschwinden. Korrigiere customer_id.
Lösung
CREATE TABLE addresses (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
city TEXT NOT NULL,
street TEXT NOT NULL
);REFERENCES customers(id) ON DELETE CASCADE. Eine Adresse ohne Kunden nützt niemandem, und sie sind personenbezogene Daten, die nicht zurückbleiben sollen.
Die Idee
Die Prüfung muss an sein
Im Kurs prüft die Datenbank Fremdschlüssel. Du fragst sie mit PRAGMA, dem Befehl von SQLite für seine Einstellungen:
1 heißt „an“. Schaltest du sie aus, bewacht REFERENCES nichts mehr:
Eine Bestellung für Kunde 99, den es nicht gibt, ging ohne Fehler hinein.
Genauer, für Neugierige
In echtem SQLite ist die Prüfung standardmäßig aus. Starte sqlite3 auf deinem Computer und tippe PRAGMA foreign_keys;: Die Antwort ist 0. Der Grund sind alte Programme, die geschrieben wurden, bevor SQLite Fremdschlüssel prüfte. Die Einstellung gilt nur für eine Verbindung zur Datenbank. Deshalb führt ein Programm bei jedem Öffnen der Datenbank PRAGMA foreign_keys = ON; aus. Diese Seite macht es für dich. PostgreSQL, MySQL (mit InnoDB) und SQL Server prüfen Fremdschlüssel standardmäßig.
Übung
Fragen zu Produkten
Die Tabelle questions hat id, product_id, customer_id und body. Wenn ein Produkt aus dem Shop genommen wird, verschwinden die Fragen dazu. Wenn ein Kunde sein Konto löscht, bleiben seine Fragen, aber ohne Autor. Ergänze die Verweise.
Lösung
CREATE TABLE questions (
id INTEGER PRIMARY KEY,
product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL,
body TEXT NOT NULL
);product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE und customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL. customer_id hat kein NOT NULL, sonst könnte SET NULL nicht funktionieren.
Test