Beziehungen und Joins

Bis hierher hatte die Bibliothek eine Tabelle. Autoren, Verlage, Ausleihen, Nutzer: sobald ein zweites Ding dazukommt, stellt sich die Frage, wie die beiden zusammenhängen.

01 / 06Warum der Name nicht in jede Buchzeile gehört

import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync(":memory:");

// Der Autorenname steht in jeder Buchzeile. Sieht harmlos aus.
db.exec(`CREATE TABLE buecher (id INTEGER PRIMARY KEY, titel TEXT NOT NULL, autor TEXT);
INSERT INTO buecher (titel, autor) VALUES
  ('Node in der Praxis', 'Adler'),
  ('HTTP verstehen', 'Adler'),
  ('Die lange Nacht des Debuggens', 'Alder'),
  ('Nachtschicht mit Node', 'Adler ');`);

console.log("Wie viele Buecher hat Adler?");
console.log("  WHERE autor = 'Adler' findet:", db.prepare("SELECT count(*) AS n FROM buecher WHERE autor = ?").get("Adler").n);

console.log("Was wirklich in der Spalte steht:");
for (const zeile of db.prepare("SELECT DISTINCT autor FROM buecher ORDER BY autor").all()) {
  console.log(`  "${zeile.autor}"`);
}

Vier Bücher, ein Autor, und die Suche findet zwei. In der Spalte stehen Adler, Alder und Adler mit einem Leerzeichen am Ende, und keine dieser drei Schreibweisen ist für die Datenbank dieselbe wie die andere.

Das passiert nicht, weil jemand schlampt. Es passiert, weil ein Wert, den man an vierzig Stellen einträgt, vierzig Gelegenheiten hat, anders auszufallen. Und es fällt lange nicht auf: Die Liste sieht richtig aus, nur das Zählen stimmt nicht.

Dazu kommt der zweite Fall. Der Autor heiratet und heißt anders. Jetzt musst du vierzig Zeilen ändern und darfst keine übersehen.

Die Lösung ist, den Namen genau einmal zu speichern. Er bekommt eine eigene Tabelle mit einer eigenen Kennung, und das Buch merkt sich nur diese Kennung. Der Name steht dann an einer Stelle, und eine Stelle kann man ändern.

02 / 06Der Verweis und was er zusagt

import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync(":memory:");

db.exec(`CREATE TABLE autoren (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE buecher (
  id INTEGER PRIMARY KEY,
  titel TEXT NOT NULL,
  autor_id INTEGER REFERENCES autoren(id)
);
INSERT INTO autoren (id, name) VALUES (1, 'Adler'), (2, 'Berger'), (3, 'Wolf');
INSERT INTO buecher (titel, autor_id) VALUES
  ('Node in der Praxis', 1),
  ('HTTP verstehen', 1),
  ('JavaScript von vorn', 2),
  ('Sicher ausliefern', NULL);`);

// INNER JOIN: nur Zeilen, zu denen es auf beiden Seiten einen Partner gibt.
console.log("INNER JOIN, Buch und Autor:");
for (const zeile of db.prepare(`SELECT buecher.titel AS titel, autoren.name AS autor
  FROM buecher INNER JOIN autoren ON autoren.id = buecher.autor_id
  ORDER BY buecher.titel`).all()) {
  console.log(`  ${zeile.titel} von ${zeile.autor}`);
}

// LEFT JOIN: alle Zeilen der linken Tabelle, auch die ohne Partner.
console.log("LEFT JOIN, alle Autoren mit ihrer Buchzahl:");
for (const zeile of db.prepare(`SELECT autoren.name AS autor, count(buecher.id) AS anzahl
  FROM autoren LEFT JOIN buecher ON buecher.autor_id = autoren.id
  GROUP BY autoren.id ORDER BY autoren.name`).all()) {
  console.log(`  ${zeile.autor}: ${zeile.anzahl}`);
}

console.log("Und das Buch ohne Autor:");
for (const zeile of db.prepare(`SELECT buecher.titel AS titel, autoren.name AS autor
  FROM buecher LEFT JOIN autoren ON autoren.id = buecher.autor_id
  WHERE autoren.id IS NULL`).all()) {
  console.log(`  ${zeile.titel} von ${zeile.autor}`);
}

autor_id INTEGER REFERENCES autoren(id) ist der ganze Verweis. Die Spalte heißt Fremdschlüssel, weil sie den Schlüssel einer fremden Tabelle enthält.

Die Zusage dahinter ist: In dieser Spalte steht entweder eine Kennung, die es in autoren wirklich gibt, oder gar nichts. Ein Buch, das auf einen Autor zeigt, den niemand angelegt hat, kommt nicht in die Tabelle. Damit ist eine ganze Sorte von kaputten Daten ausgeschlossen, und zwar nicht durch Disziplin, sondern durch die Datenbank.

03 / 06Die Eigenheit, die einen überrascht

Und jetzt der Teil, bei dem SQLite anders ist als fast alles, was man darüber liest.

import { DatabaseSync } from "node:sqlite";

const aufbau = `CREATE TABLE autoren (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE buecher (id INTEGER PRIMARY KEY, titel TEXT NOT NULL, autor_id INTEGER REFERENCES autoren(id));
INSERT INTO autoren (id, name) VALUES (1, 'Adler');`;

const probe = (db, was) => {
  const an = db.prepare("PRAGMA foreign_keys").get().foreign_keys;
  try {
    db.prepare("INSERT INTO buecher (titel, autor_id) VALUES (?, ?)").run("Geisterbuch", 99);
    console.log(`${was} (PRAGMA foreign_keys = ${an}): durchgelassen`);
  } catch (fehler) {
    console.log(`${was} (PRAGMA foreign_keys = ${an}): abgewiesen, ${fehler.message}`);
  }
};

const standard = new DatabaseSync(":memory:");
standard.exec(aufbau);
probe(standard, "node:sqlite mit Vorgabe   ");

const abgeschaltet = new DatabaseSync(":memory:", { enableForeignKeyConstraints: false });
abgeschaltet.exec(aufbau);
probe(abgeschaltet, "ausdruecklich abgeschaltet");

SQLite prüft Fremdschlüssel von Haus aus nicht. Aus historischen Gründen ist die Prüfung abschaltbar und war jahrelang standardmäßig aus. Deshalb findet man überall den Rat, in jeder Verbindung PRAGMA foreign_keys = ON abzusetzen, und deshalb erzählen viele Anleitungen, ohne diese Zeile sei der Fremdschlüssel bloße Dekoration.

In node:sqlite stimmt das nicht. DatabaseSync schaltet die Prüfung ein, gemessen steht PRAGMA foreign_keys dort auf 1, und ein Buch mit einem erfundenen Autor wird abgewiesen. Wer sie loswerden will, muss das ausdrücklich sagen.

Der Unterschied ist wichtig genug, um ihn zu kennen, denn er läuft in beide Richtungen: Wer sich darauf verlässt, dass die Prüfung an ist, bekommt in der sqlite3-Kommandozeile oder mit einer anderen Bibliothek eine Überraschung. Die Kommandozeile liegt in diesem Kurs übrigens nicht bei, und du brauchst sie auch nicht: Der verlässliche Weg ist, die Datenbank selbst zu fragen. Die Abfrage dafür steht im Beispiel und passt in eine Zeile.

04 / 06INNER JOIN und LEFT JOIN

Ein Join führt die beiden Tabellen zusammen. ON sagt, welche Spalten dabei zusammengehören, und das ist praktisch immer die Kennung auf der einen und der Fremdschlüssel auf der anderen Seite.

INNER JOIN liefert nur Zeilen mit Partner. Im Beispiel sind das drei Bücher. „Sicher ausliefern” hat keinen Autor und fällt heraus, und Wolf hat kein Buch und fällt ebenfalls heraus.

LEFT JOIN behält alle Zeilen der linken Tabelle. Steht autoren links, kommen alle Autoren vor, auch Wolf mit seinen null Büchern. Genau dafür nimmt man ihn: für Übersichten, in denen die Leeren mitgezählt werden sollen.

Die Fangfrage dazu beantwortet die dritte Abfrage im Beispiel. Wo es keinen Partner gibt, füllt der LEFT JOIN die Spalten der rechten Seite mit NULL, und in JavaScript kommt das als null an. Man kann danach also suchen: WHERE autoren.id IS NULL findet genau die Zeilen ohne Partner.

Vorsicht bei count() im LEFT JOIN. Im Beispiel steht count(buecher.id) und nicht count(*). Der Unterschied ist genau Wolf: count(*) zählt Zeilen und käme auf 1, weil die Join-Zeile ja existiert, nur eben mit lauter NULL darin. count(buecher.id) zählt Werte und überspringt NULL, und deshalb steht dort die richtige 0.

05 / 06Spalten benennen, sobald zwei Tabellen im Spiel sind

Beide Tabellen haben eine Spalte id. Schreibst du im SELECT einfach id, weiß niemand, welche gemeint ist, und du bekommst je nach Lage einen Fehler oder die falsche.

Deshalb steht in den Beispielen überall buecher.titel und autoren.name, und hinter jeder Spalte ein AS. Der Punkt sagt, aus welcher Tabelle sie kommt, das AS sagt, wie sie in JavaScript heißen soll. Beides kostet nichts und erspart eine Sorte Fehler ganz.

06 / 06Die drei Beziehungsarten in je einem Satz

Eins zu vielen ist der Fall aus dieser Lektion: Ein Autor hat viele Bücher, ein Buch hat einen Autor. Der Fremdschlüssel steht bei den vielen, also in buecher.

Eins zu eins ist selten und meist eine Auslagerung: Ein Nutzer hat genau ein Profil. Man macht es, wenn die zweite Tabelle große oder selten gebrauchte Felder hält.

Viele zu viele ist der Fall, bei dem beide Seiten mehrere haben können: Ein Buch hat mehrere Schlagwörter, ein Schlagwort hängt an mehreren Büchern. Dafür gibt es keine Spalte, sondern eine dritte Tabelle mit zwei Fremdschlüsseln, in der jede Zeile eine Verbindung ist.