Beziehungen abfragen und Änderungen absichern

Voraussetzungen und Lernziele

Du kennst Tabellen, Zeilen, Primärschlüssel und einfache SELECT-Abfragen. Diese Lektion verwendet SQLite als lokal einfach ausführbares Übungssystem. Nach 150 Minuten kannst du eine n:m-Beziehung modellieren, INNER JOIN und LEFT JOIN unterscheiden und eine zusammengehörige Änderung mit COMMIT oder ROLLBACK abschliessen. Plane 45 Minuten Theorie und 105 Minuten für die Abfragen und Transferaufgabe.

Beziehungen ausdrücklich speichern

Ein Lehrgang hat viele Lernende; eine Person kann mehrere Lehrgänge besuchen. Eine kommagetrennte Liste von Kursnummern in einer Personentabelle wirkt zunächst bequem. Sie erschwert aber die Prüfung, ob jede Nummer einen existierenden Kurs bezeichnet, und macht einzelne Einschreibungen umständlich zu ändern.

Eine Zwischentabelle macht die Beziehung zu eigenen Daten:

PRAGMA foreign_keys = ON;
CREATE TABLE learners (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE courses (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL
);
CREATE TABLE enrollments (
  learner_id INTEGER NOT NULL REFERENCES learners(id),
  course_id INTEGER NOT NULL REFERENCES courses(id),
  PRIMARY KEY (learner_id, course_id)
);
INSERT INTO learners VALUES (1, 'Ada'), (2, 'Linus');
INSERT INTO courses VALUES (10, 'SQL'), (20, 'Netzwerke');
INSERT INTO enrollments VALUES (1, 10), (1, 20);

Das Skript gehört in eine neue, eigene Übungsdatenbank. Die beiden Kennungen bilden gemeinsam den Primärschlüssel der Beziehung: Dieselbe Person darf sich nicht zweimal in denselben Kurs einschreiben. Fremdschlüssel sichern referenzielle Konsistenz. Bei SQLite wird ihre Durchsetzung pro Verbindung ausdrücklich aktiviert; verlasse dich nicht auf eine unbekannte Voreinstellung des Werkzeugs.

Das Modell lautet:

learners 1 ── n enrollments n ── 1 courses

Dieses Diagramm sagt nichts über die gewünschte Benutzeroberfläche aus. Es beschreibt zulässige Beziehungen im gespeicherten Zustand.

Zusammenführen bedeutet nicht zusammenkopieren

Ein JOIN bildet ein Abfrageergebnis aus passenden Zeilen. Er verändert die Tabellen nicht:

SELECT l.name, c.title
FROM learners AS l
JOIN enrollments AS e ON e.learner_id = l.id
JOIN courses AS c ON c.id = e.course_id
ORDER BY l.id, c.id;

Das Ergebnis enthält Ada mit SQL und Ada mit Netzwerke. Linus fehlt, weil keine passende Einschreibung existiert. Das ist für die Frage «Welche belegten Kurse gibt es?» korrekt, für «Zeige alle Lernenden» aber unvollständig.

Ein LEFT JOIN erhält auch die linke Zeile ohne Treffer:

SELECT l.name, COUNT(e.course_id) AS course_count
FROM learners AS l
LEFT JOIN enrollments AS e ON e.learner_id = l.id
GROUP BY l.id, l.name
ORDER BY l.id;

Ada erhält 2, Linus 0. COUNT(e.course_id) zählt vorhandene Werte dieser Spalte. COUNT(*) zählt hingegen auch die durch den LEFT JOIN erhaltene Zeile ohne Einschreibung und würde hier für Linus 1 ergeben. Ein kleiner Unterschied im Ausdruck verändert also die fachliche Aussage.

Ein Filter kann die Aussage verändern

Schreibst du nach einem LEFT JOIN WHERE e.course_id = 10, scheiden Zeilen ohne passende Einschreibung aus: Ihr Wert erfüllt die Bedingung nicht. Wenn du alle Lernenden erhalten, aber nur die Einschreibung in Kurs 10 betrachten möchtest, gehört die Einschränkung in die Verknüpfungsbedingung:

SELECT l.name, e.course_id
FROM learners AS l
LEFT JOIN enrollments AS e
  ON e.learner_id = l.id AND e.course_id = 10;

Die Frage muss also vor der Abfrage feststehen. SQL-Syntax allein verrät nicht, ob das Ergebnis zur fachlichen Absicht passt.

Mehrere Änderungen als Einheit

Angenommen, ein Kurswechsel soll die alte Einschreibung entfernen und eine neue anlegen. Nach nur der ersten Änderung wäre ein unvollständiger Zwischenzustand dauerhaft gespeichert. Eine explizite Transaktion kann die beiden Schritte als Einheit behandeln:

BEGIN;
DELETE FROM enrollments WHERE learner_id = 1 AND course_id = 20;
INSERT INTO enrollments (learner_id, course_id) VALUES (1, 10);
ROLLBACK;

Die Einfügung verletzt mit den obigen Ausgangsdaten absichtlich den zusammengesetzten Primärschlüssel, weil Ada Kurs 10 bereits besucht. Bei dieser Übung wird danach ausdrücklich zurückgerollt. Die Anwendung muss Fehler behandeln und darf nach einer Ausnahme nicht versehentlich einen teilweisen Zustand bestätigen. Prüfe anschliessend, dass beide ursprünglichen Einschreibungen noch existieren.

Für einen erfolgreichen Wechsel nach Kurs 30 legst du diesen Kurs zuerst an, beginnst eine neue Transaktion, entfernst Kurs 20, fügst Kurs 30 ein und verwendest dann COMMIT. Führe das erst aus, wenn du den erwarteten Zustand auf Papier notiert hast.

SQLite erlaubt mehrere gleichzeitige Lesetransaktionen, aber nur eine gleichzeitige Schreibtransaktion. Eine Transaktion beseitigt deshalb weder jede Wartezeit noch jeden möglichen Konflikt. Bei anderen Datenbanksystemen gelten andere Einzelheiten zu Sperren und Isolation. Übertrage diese Übung nicht unbesehen auf eine produktive MySQL- oder PostgreSQL-Anwendung.

Verständnisfragen

1. Warum entstehen für Ada zwei Ergebniszeilen, ohne dass ihre Person doppelt gespeichert ist?
2. Weshalb liefert COUNT(*) beim beschriebenen LEFT JOIN nicht die gewünschte Kursanzahl?
3. Genügt BEGIN ohne Fehlerbehandlung für einen sicheren Kurswechsel?

Erklärungen

1. Eine Person kann zu mehreren Beziehungen passen. Die Ergebniszeilen stellen Verknüpfungen dar, keine neuen Personendatensätze.
2. Auch eine erhaltene linke Zeile ohne Beziehung zählt als Ergebniszeile. Die nicht vorhandene rechte Kennung wird dagegen von COUNT(Spalte) nicht gezählt.
3. Nein. Die Anwendung muss erfolgreiche Abläufe bestätigen und fehlgeschlagene Abläufe bewusst zurückrollen. Sie muss auch wissen, wie ihr Treiber bei Fehlern reagiert.

Übung und gestufte Hilfen

Ergänze eine dritte Person ohne Einschreibung und einen dritten Kurs ohne Teilnehmende. Erstelle erstens eine Liste aller Kurse mit Teilnehmerzahl und zweitens eine Liste aller Personen ohne Einschreibung. Notiere jeweils die erwarteten Zeilen vor der Ausführung.

SELECT c.id, c.title, COUNT(e.learner_id) AS participants
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.id
GROUP BY c.id, c.title
ORDER BY c.id;

SELECT l.id, l.name
FROM learners l
LEFT JOIN enrollments e ON e.learner_id = l.id
WHERE e.learner_id IS NULL
ORDER BY l.id;

Transfer und Zusammenfassung

Entwirf ein Modell für Bücher und mehrere Autorinnen oder Autoren. Welche doppelte Beziehung muss verhindert werden? Welche Abfrage findet Bücher ohne zugeordnete Person? Welche fachlichen Regeln sind durch Fremdschlüssel allein noch nicht ausgedrückt?

JOINs beantworten Beziehungsfragen. Ein gutes Datenmodell und passende Constraints grenzen zulässige Zustände ein. Transaktionen halten zusammengehörige Änderungen zusammen, während die Anwendung weiterhin Fehler und Berechtigungen verantwortlich behandeln muss.

Quellen und Prüfung

Primärquellen zuletzt inhaltlich konsultiert am 8. November 2026. Versionsangaben beachten; eine Dokumentationsseite kann sich später ändern.

• SQLite: SELECT
• SQLite: Foreign Key Support
• SQLite: Transactions

Konto erstellen, um deinen Bearbeitungsstand zu speichern.