Fremdschlüssel und referenzielle Integrität

Garantiere die Konsistenz zwischen deinen Tabellen mit REFERENCES und wähle das ON DELETE-Verhalten bewusst.

8 Min. · 3 Fragen

Ein Fremdschlüssel deklariert, dass eine Spalte auf den Primärschlüssel einer anderen Tabelle zeigt. Die Datenbank verweigert dann jeden verwaisten Wert: Du kannst kein Buch anlegen, dessen Autor nicht existiert. Achtung, in SQLite ist diese Prüfung standardmäßig deaktiviert: Aktiviere sie mit einem PRAGMA am Anfang deines Skripts.

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author_id INTEGER NOT NULL
    REFERENCES authors(id)
    ON DELETE CASCADE
);

INSERT INTO authors (id, name) VALUES
  (1, 'Ursula K. Le Guin'),
  (2, 'Ted Chiang');

INSERT INTO books (id, title, author_id) VALUES
  (1, 'A Wizard of Earthsea', 1),
  (2, 'The Dispossessed', 1),
  (3, 'Exhalation', 2);

Teste den Schutz: Füge ein verwaistes Buch ein, lösche dann einen Autor. Die erste Anweisung wird verweigert; die zweite nimmt dank ON DELETE CASCADE ihre Bücher mit sich.

INSERT INTO books (id, title, author_id)
VALUES (4, 'Orphan', 99);

DELETE FROM authors WHERE id = 1;

SELECT id, title FROM books;

ON DELETE bestimmt das Schicksal der Kindzeilen, wenn der Elterndatensatz verschwindet. CASCADE löscht sie mit ihm. SET NULL behält die Zeile, löscht aber die Referenz - die Spalte muss also NULL akzeptieren. Ohne Klausel wendet SQLite NO ACTION an: Das Löschen des Elterndatensatzes wird verweigert, solange Kinder verbleiben, ein Verhalten nahe an RESTRICT.

CREATE TABLE articles (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  reviewer_id INTEGER
    REFERENCES authors(id)
    ON DELETE SET NULL
);

Wissenscheck

Stelle sicher, dass du die wichtigsten Punkte dieser Lektion behalten hast.

1. Was bewirkt DELETE FROM authors WHERE id = 1 bei ON DELETE CASCADE auf books.author_id?
   • Einen Fehler, solange der Autor Bücher hat
   • Das Löschen des Autors und aller seiner Bücher
   • Das Setzen von author_id auf NULL in seinen Büchern
2. Warum sollte man PRAGMA foreign_keys = ON; in SQLite ausführen?
   • Um die Fremdschlüssel zu erstellen
   • Weil die Fremdschlüsselprüfung standardmäßig deaktiviert ist, Verbindung für Verbindung
   • Um Joins zu beschleunigen
3. Welche Bedingung muss die Spalte reviewer_id erfüllen, um ON DELETE SET NULL zu verwenden?
   • UNIQUE deklariert sein
   • Vom Typ INTEGER sein
   • NULL akzeptieren, also kein NOT NULL tragen