Fremdschlüssel

In Lektion 1 hast du Fremdschlüsseln von Hand gefolgt: Die kunde_id einer Bestellung ist die id eines Kunden. Ein Fremdschlüssel ist eine Spalte, deren Werte die Primärschlüssel einer anderen Tabelle sind. Wenn du eine Tabelle erstellst, kennzeichnest du ihn mit REFERENCES, gefolgt von der Tabelle und der Spalte, auf die er zeigt. Zum Beispiel Notizen des Supports des Shops über seine Kunden:

CREATE TABLE kundennotizen (
    id INTEGER PRIMARY KEY,
    kunde_id INTEGER NOT NULL REFERENCES kunden (id),
    notiz TEXT NOT NULL
) STRICT;

Von nun an stellt die Datenbank sicher, dass jede kunde_id in der Tabelle die id eines Kunden ist, den es gibt. Eine Notiz über Kunde 3 ist in Ordnung; eine Notiz über Kunde 99, den es nicht gibt, wird abgelehnt:

INSERT INTO kundennotizen (kunde_id, notiz)
VALUES (3, 'Mag Puzzlespiele.');

INSERT INTO kundennotizen (kunde_id, notiz)
VALUES (99, 'Hat nach einer Erstattung gefragt.');

Error on line 4: FOREIGN KEY constraint failed

Anders als die anderen Meldungen sagt diese nicht, welche Spalte falsch ist, du musst also den Wert, der ins Leere zeigt, selbst finden. NULL ist in einem Fremdschlüssel erlaubt, es sei denn, die Spalte hat NOT NULL: Er zeigt dann auf gar nichts, wie das geworben_von eines Kunden, dem niemand den Shop empfohlen hat.

Die Prüfung funktioniert auch in die andere Richtung. In der letzten Lektion hast du gesehen, dass ein Spiel mit Bestellungen nicht gelöscht werden kann: Die Zeilen von positionen zeigen darauf. Dadurch zeigt nie eine Zeile auf etwas, das nicht da ist. Wenn du lieber hättest, dass die Zeilen, die auf eine gelöschte Zeile zeigen, mit ihr gelöscht werden, schreibe ON DELETE CASCADE nach REFERENCES kunden (id). Verwende es vorsichtig, nur für Zeilen, die allein keinen Sinn ergeben.

Ein Fremdschlüssel hat den Typ der Spalte, auf die er zeigt, hier INTEGER, und sein Name sagt meist, wohin er zeigt: die Tabelle im Singular und _id, wie in kunde_id oder spiel_id. Du kannst einen Fremdschlüssel auch nach der letzten Spalte geschrieben sehen, als FOREIGN KEY (kunde_id) REFERENCES kunden (id); er bedeutet dasselbe.

SQLite prüft Fremdschlüssel nur, wenn man es ihm mit PRAGMA foreign_keys = ON sagt. Dieser Kurs erledigt das vor jedem Ausführen für dich; andere Datenbanken prüfen sie immer. Und die neue Tabelle erscheint nicht im Tab Tabellen, der nur die Tabellen zeigt, mit denen der Shop beginnt.

Typischer Fehler: ein Tippfehler in der Tabelle nach REFERENCES. CREATE TABLE prüft sie nicht, der Fehler zeigt sich also erst beim ersten INSERT:

CREATE TABLE kundennotizen (
    id INTEGER PRIMARY KEY,
    kunde_id INTEGER NOT NULL REFERENCES kunde (id),
    notiz TEXT NOT NULL
) STRICT;

INSERT INTO kundennotizen (kunde_id, notiz)
VALUES (3, 'Mag Puzzlespiele.');

Error on line 7: no such table: main.kunde