SQL: Beziehungen und Fremdschlüssel

Kurz: Ein Kunde mit vielen Bestellungen soll nicht in jeder Bestellzeile seine Adresse wiederholen. Besser: zwei Tabellen, verbunden über einen Fremdschlüssel.

Teil des Kurses SQL

Kapitel 7 von 15 im Kurs SQL (Abschnitt „Mehrere Tabellen“). Mit Fortschritt, Quiz und Zertifikat auf der Lernseite.

Warum mehrere Tabellen?

Ein Kunde mit vielen Bestellungen soll nicht in jeder Bestellzeile seine Adresse wiederholen. Besser: zwei Tabellen, verbunden über einen Fremdschlüssel.

Beziehung	Beispiel	Umsetzung
1:n (eins zu viele)	Kunde hat viele Bestellungen	Fremdschlüssel in der “n”-Tabelle
n:m (viele zu viele)	Studenten belegen Kurse	Verbindungstabelle mit zwei Fremdschlüsseln
1:1	Nutzer hat ein Profil	Fremdschlüssel mit UNIQUE

Fremdschlüssel

Ein Fremdschlüssel (foreign key) verweist auf den Primärschlüssel einer anderen Tabelle. Die Datenbank prüft dann, dass der Verweis gültig ist (in SQLite mit PRAGMA foreign_keys = ON):

PRAGMA foreign_keys = ON;
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 NOT NULL REFERENCES autoren(id)
);
INSERT INTO autoren VALUES (1, 'Kafka'), (2, 'Mann');
INSERT INTO buecher VALUES (1, 'Der Prozess', 1), (2, 'Das Schloss', 1), (3, 'Buddenbrooks', 2);
SELECT * FROM buecher;

Ausgabe:

id  titel         autor_id
--  ------------  --------
1   Der Prozess   1
2   Das Schloss   1
3   Buddenbrooks  2

Ein Buch mit nicht vorhandenem Autor wird abgelehnt:

PRAGMA foreign_keys = ON;
CREATE TABLE autoren (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE buecher (id INTEGER PRIMARY KEY, autor_id INTEGER REFERENCES autoren(id));
INSERT INTO buecher VALUES (1, 99);

Fehlermeldung:

Runtime error near line 4: FOREIGN KEY constraint failed (19)

Was passiert beim Löschen?

Mit ON DELETE legst du das Verhalten fest:

Option	Wirkung
RESTRICT / NO ACTION	Löschen verboten, solange Verweise existieren (Standard)
CASCADE	abhängige Zeilen mitlöschen
SET NULL	Verweis auf NULL setzen

PRAGMA foreign_keys = ON;
CREATE TABLE kunde (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE bestellung (
    id INTEGER PRIMARY KEY,
    kunde_id INTEGER REFERENCES kunde(id) ON DELETE CASCADE,
    produkt TEXT
);
INSERT INTO kunde VALUES (1, 'Mia'), (2, 'Tom');
INSERT INTO bestellung VALUES (1, 1, 'Maus'), (2, 1, 'Laptop'), (3, 2, 'Monitor');
DELETE FROM kunde WHERE id = 1;
SELECT * FROM bestellung;

Ausgabe:

id  kunde_id  produkt
--  --------  -------
3   2         Monitor

n:m-Beziehung mit Verbindungstabelle

PRAGMA foreign_keys = ON;
CREATE TABLE studenten (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE kurse (id INTEGER PRIMARY KEY, titel TEXT);
CREATE TABLE belegungen (
    student_id INTEGER REFERENCES studenten(id),
    kurs_id    INTEGER REFERENCES kurse(id),
    note REAL,
    PRIMARY KEY (student_id, kurs_id)
);
INSERT INTO studenten VALUES (1,'Mia'),(2,'Tom');
INSERT INTO kurse VALUES (1,'SQL'),(2,'Python');
INSERT INTO belegungen VALUES (1,1,1.3),(1,2,2.0),(2,1,2.7);
SELECT * FROM belegungen;

Ausgabe:

student_id  kurs_id  note
----------  -------  ----
1           1        1.3
1           2        2.0
2           1        2.7

Der zusammengesetzte Primärschlüssel verhindert, dass derselbe Student denselben Kurs doppelt belegt.

Schlüsselarten

Art	Bedeutung
Primärschlüssel	identifiziert eine Zeile eindeutig, nie NULL
Fremdschlüssel	verweist auf Primärschlüssel einer anderen Tabelle
Surrogatschlüssel	künstliche ID (id, auto-inkrement oder UUID)
natürlicher Schlüssel	Fachwert wie ISBN
zusammengesetzter Schlüssel	mehrere Spalten zusammen

Merke

• 1:n über einen Fremdschlüssel, n:m über eine Verbindungstabelle
• Fremdschlüssel sichern Datenintegrität (SQLite: PRAGMA foreign_keys = ON)
• ON DELETE CASCADE / SET NULL / RESTRICT steuert das Löschverhalten
• Jede Tabelle hat einen Primärschlüssel

Übungsaufgabe

Modelliere Blogposts und Kommentare (1:n) mit Fremdschlüssel und ON DELETE CASCADE.

Quiz zur Selbstkontrolle

Wie setzt man eine n:m-Beziehung um?

• Mit einer Verbindungstabelle (richtig)
• Mit einer Spalte
• Mit zwei Datenbanken
• Gar nicht

Was bewirkt ON DELETE CASCADE?

• Abhängige Zeilen werden mitgelöscht (richtig)
• Das Löschen wird verboten
• Nichts
• Die Tabelle wird gelöscht

Was verweist ein Fremdschlüssel auf?

• Den Primärschlüssel einer anderen Tabelle (richtig)
• Eine Datei
• Einen Index
• Eine Ansicht

Weiter im Kurs

Zurück: Aggregation und GROUP BY

Weiter: JOINs

Alle Kapitel: SQL im Überblick