mitmario.dev

Beziehungen und Joins

Node.js Sandbox 4 Min Lesezeit 3 BeispieleLektion 7 von 9

Zu einem Buch gehört ein Autor, zu einer Ausleihe ein Buch und eine Person. Sobald ein zweites Ding dazukommt, fragt sich, wie die beiden zusammenhängen. Naheliegend ist, den Namen des Autors einfach in jede Buchzeile zu schreiben, und das geht schief.

Warum der Name nicht in jede Buchzeile gehört

Derselbe Name in vielen Zeilen
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 Bücher 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 autor, count(*) AS n FROM buecher GROUP BY autor ORDER BY autor").all()) {
  console.log(`  "${zeile.autor}": ${zeile.n}`);
}

Es sind vier Bücher von einem Autor, und die Suche findet zwei. In der Spalte stehen Adler, Alder und Adler mit einem Leerzeichen am Ende. Für die Datenbank sind das drei verschiedene Schreibweisen.

Dafür muss niemand schlampen. Ein Wert, den man an vierzig Stellen einträgt, hat vierzig Gelegenheiten, anders auszufallen. Lange fällt es nicht einmal auf. Die Liste sieht richtig aus, nur das Zählen stimmt nicht.

Ändert der Autor seinen Namen, müssen alle vierzig Zeilen mit, und keine darf fehlen.

Besser steht der Name nur einmal da, in einer eigenen Tabelle mit einer eigenen Kennung. Das Buch merkt sich nur diese Kennung. Heißt der Autor dann anders, ändert sich eine Zeile.

Der Fremdschlüssel

Zwei Tabellen, ein Verweis
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, Autoren mit ihrer Buchzahl:");
for (const zeile of db.prepare(`SELECT autoren.name AS autor, count(buecher.id) AS anzahl
  FROM autoren INNER JOIN buecher ON buecher.autor_id = autoren.id
  GROUP BY autoren.id ORDER BY autoren.name`).all()) {
  console.log(`  ${zeile.autor}: ${zeile.anzahl}`);
}

// 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 (englisch foreign key), weil sie den Schlüssel einer fremden Tabelle enthält.

In dieser Spalte darf dann nur eine Kennung stehen, 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. Die Datenbank hält damit eine ganze Sorte kaputter Daten draußen, und niemand muss dafür aufpassen.

Die Prüfung der Fremdschlüssel

Was der Fremdschlüssel verhindert
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, "ausdrücklich abgeschaltet");

SQLite selbst prüft Fremdschlüssel von Haus aus nicht. In der Dokumentation von SQLite steht dazu, die Prüfung sei aus Rücksicht auf ältere Anwendungen ausgeschaltet und müsse für jede Verbindung eigens eingeschaltet werden, mit PRAGMA foreign_keys = ON.

In node:sqlite ist das anders. DatabaseSync schaltet die Prüfung ein, PRAGMA foreign_keys steht dort auf 1, und ein Buch mit einem erfundenen Autor wird abgewiesen. Willst du sie loswerden, musst du das ausdrücklich sagen.

Verlässt du dich darauf, dass die Prüfung an ist, erlebst du in der sqlite3-Kommandozeile oder mit einem anderen Treiber eine Überraschung. Die Kommandozeile liegt in diesem Kurs nicht bei. Gebraucht wird sie auch nicht, denn verlässlich ist ohnehin nur die Frage an die Datenbank selbst. Die Abfrage dafür steht im Beispiel und passt in eine Zeile.

INNER 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 stellt er dieselbe Frage wie der LEFT JOIN darunter, die Buchzahl je Autor, und Wolf fehlt, denn zu ihm gibt es keine Buchzeile, mit der er sich verbinden ließe.
  • LEFT JOIN behält alle Zeilen der linken Tabelle. Steht autoren links, kommen alle Autoren vor, auch Wolf mit seinen null Büchern. Man nimmt ihn für Übersichten, in denen die Leeren mitgezählt werden sollen.

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. Die dritte Abfrage im Beispiel sucht danach, und WHERE autoren.id IS NULL findet die Bücher ohne Autor.

Bei count() im LEFT JOIN zählt, was in der Klammer steht. Im Beispiel steht count(buecher.id) und nicht count(*). Der Unterschied ist 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.

Spalten benennen, sobald zwei Tabellen im Spiel sind

Beide Tabellen haben eine Spalte id. Steht im SELECT einfach id, ist nicht klar, welche gemeint ist, und SQLite bricht mit ambiguous column name: id ab.

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.

Die drei Beziehungsarten

  • Eins zu vielen ist der Fall aus dieser Lektion. Ein Autor hat viele Bücher, ein Buch hat einen Autor, und der Fremdschlüssel steht bei den vielen, also in buecher.
  • Eins zu eins ist selten und meist eine Auslagerung, etwa ein Nutzer mit genau einem Profil. Man macht es, wenn die zweite Tabelle große oder selten gebrauchte Spalten hält.
  • Viele zu viele heißt, dass beide Seiten mehrere haben können. Ein Buch hat mehrere Schlagwörter, ein Schlagwort hängt an mehreren Büchern. Dafür gibt es eine dritte Tabelle mit zwei Fremdschlüsseln, in der jede Zeile eine Verbindung ist.

Beide Tabellen samt Verweis und zwei Abfragen darauf schreibst du in der Aufgabe selbst.

Zum Mitnehmen

Ein Autor steht einmal in seiner eigenen Tabelle, und die Bücher verweisen über einen Fremdschlüssel auf ihn, den node:sqlite von sich aus prüft. Ein INNER JOIN lässt Autoren ohne Buch weg, ein LEFT JOIN behält sie und zählt sie mit count(buecher.id) als 0.

Jetzt du

Basis Konto, kostenlos

Zu dieser Lektion gehört eine Aufgabe. Du schreibst den Code selbst, und nach jedem Lauf sagt dir eine Prüfliste, was schon stimmt.

Dafür brauchst du das Basis Konto. Es kostet nichts, und ein Passwort gibt es auch nicht.

In diesem Kurs läuft dein Code auf einem Server. Dafür hat das Basis Konto 1 Stunde im Monat, mehr Zeit gibt es mit dem Premium Konto.

Was in dieser Lektion steckt

  • Artikel mit 3 Beispielen zum Ausprobieren

    Steht hier, ohne Konto lesbar.

  • Aufgabe, dein Code läuft auf einem Server

    Öffnet sich mit dem Basis Konto.