Leitfaden zu Datenbankbeziehungen

Eine Datenbankbeziehung verknüpft Zeilen einer Tabelle mit Zeilen einer anderen über einen Fremdschlüssel: eine Spalte, die den Primärschlüssel der zugehörigen Zeile enthält. Es gibt drei Arten von Beziehungen, 1:1, 1:n und n:m, und alle drei werden aus Fremdschlüsseln gebaut. Die Beispiele funktionieren in PostgreSQL, MySQL, SQLite und SQL Server.

So erstellst du Datenbankbeziehungen

Gib der Kindtabelle eine Spalte für die id des Elternteils und deklariere sie als Fremdschlüssel, damit die Datenbank Werte ablehnt, die zu keiner echten Elternzeile passen:

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Joine über den Fremdschlüssel, um zusammengehörige Zeilen zusammenzubringen. Der Constraint verhindert außerdem Zeilen, die ins Leere zeigen würden:

-- Input: customers
-- id | name
-- ---+-----------
-- 1  | Ana Lima
-- 2  | Ben Okafor

-- Input: orders
-- id | customer_id | total
-- ---+-------------+------
-- 10 | 1           | 25.00
-- 11 | 2           | 15.50
-- 12 | 1           | 40.00

SELECT c.name, o.id AS order_id, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.id;

-- Output:
-- name       | order_id | total
-- -----------+----------+------
-- Ana Lima   | 10       | 25.00
-- Ben Okafor | 11       | 15.50
-- Ana Lima   | 12       | 40.00

INSERT INTO orders (id, customer_id, total) VALUES (13, 99, 10.00);
-- ERROR: insert or update on table "orders" violates foreign key constraint
-- "orders_customer_id_fkey"

DELETE FROM customers WHERE id = 1;
-- ERROR: update or delete on table "customers" violates foreign key constraint
-- "orders_customer_id_fkey" on table "orders"

Wichtige Regeln

• SQLite ignoriert Fremdschlüssel, solange du sie nicht einschaltest. Führe PRAGMA foreign_keys = ON; bei jeder Verbindung aus, sonst geht das fehlerhafte INSERT oben durch.
• Schreib den Fremdschlüssel als eigene Zeile FOREIGN KEY (...) REFERENCES ..., wie oben. MySQL 8.0 ignoriert stillschweigend ein REFERENCES, das direkt an der Spalte steht (customer_id INTEGER REFERENCES customers(id)), sodass fehlerhafte Zeilen direkt reinrutschen.
• Leg fest, was beim Löschen eines Elternteils passiert. Standardmäßig verweigert die Datenbank das, wie oben. Füge dem Fremdschlüssel ON DELETE CASCADE hinzu, um die Kindzeilen mitzulöschen, oder ON DELETE SET NULL, um sie ohne Elternteil zu behalten.
• Indiziere die Fremdschlüsselspalte. Du wirst ständig darüber joinen und filtern, und nur MySQL legt den Index automatisch an: CREATE INDEX idx_orders_customer_id ON orders (customer_id);
• Gib ihm denselben Typ wie dem Schlüssel, auf den er zeigt, etwa INTEGER für eine INTEGER-id.
• Speichere nie eine Liste von ids in einer Spalte, etwa '1,3,5'. Die Datenbank kann das weder prüfen noch darüber joinen. Das ist eine n:m-Beziehung, und die braucht eine Verknüpfungstabelle (siehe unten).

Alternative Syntax

Gib dem Constraint einen Namen, damit seine Fehlermeldungen leichter zu lesen sind und du ihn später einfacher löschen kannst:

CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id)

Um einer bestehenden Tabelle einen Fremdschlüssel hinzuzufügen, nutze ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id);. SQLite kann das nicht: Erstelle eine neue Tabelle mit dem Fremdschlüssel, kopiere die Zeilen hinüber und tausche die Tabellen aus.

Für ids, die die Datenbank selbst vergibt, nutze GENERATED ALWAYS AS IDENTITY (oder SERIAL) in PostgreSQL, AUTO_INCREMENT in MySQL, IDENTITY(1,1) in SQL Server und INTEGER PRIMARY KEY in SQLite.

Wann solltest du Datenbankbeziehungen nutzen?

Nutze eine Beziehung immer dann, wenn zwei Arten von Dingen zusammenhängen. Welche Art du brauchst, hängt davon ab, wie viele Zeilen auf jeder Seite stehen können.

Eine Tabelle in zwei aufteilen: 1:1

Jede Zeile passt zu höchstens einer Zeile in der anderen Tabelle. Füge der zweiten Tabelle einen UNIQUE-Fremdschlüssel hinzu. Nutze das, um optionale oder selten genutzte Spalten aus einer breiten Tabelle auszulagern oder sensible Daten (Nutzer und ihre Profile, Mitarbeitende und ihre Gehaltsdaten) unter separaten Berechtigungen zu halten.

Einer Zeile viele Kinder geben: 1:n

Die häufigste Beziehung: Ein Kunde hat viele Bestellungen, ein Autor viele Beiträge. Setz einen einfachen Fremdschlüssel auf die „n“-Seite. Das Elternteil kann sogar dieselbe Tabelle sein, etwa wenn employees.manager_id auf einen anderen Mitarbeiter zeigt.

Viele Zeilen mit vielen anderen verknüpfen: n:m

Studierende belegen viele Kurse und Kurse haben viele Studierende, also kann keine Tabelle die id der anderen speichern. Füge eine Verknüpfungstabelle mit einem Fremdschlüssel zu jeder Seite und einem Primärschlüssel über das Paar hinzu, damit dieselbe Verknüpfung nicht doppelt gespeichert werden kann. Angaben zur Verknüpfung selbst, etwa eine Note, gehören in die Verknüpfungstabelle.

Zeilen ohne Gegenstück finden

Eine Beziehung zeigt auch, was fehlt. Ein LEFT JOIN über den Fremdschlüssel behält jede Zeile der ersten Tabelle und füllt NULLs ein, wo nichts passt. Filterst du nach NULL, findest du Kunden ohne Bestellungen oder Kurse, die niemand belegt hat.

Echte Beispiele für Datenbankbeziehungen

Nutzer und ihre Profile

profiles.user_id ist UNIQUE, also kann ein Nutzer kein zweites Profil bekommen. Der LEFT JOIN behält Nutzer, die noch keins angelegt haben:

-- profiles.user_id is declared as: user_id INTEGER UNIQUE NOT NULL

-- Input: users
-- id | username
-- ---+---------
-- 1  | mia
-- 2  | raj
-- 3  | zoe

-- Input: profiles
-- id | user_id | bio
-- ---+---------+---------------------
-- 1  | 1       | Software developer
-- 2  | 2       | Marketing specialist

SELECT u.username, p.bio
FROM users u
LEFT JOIN profiles p ON p.user_id = u.id
ORDER BY u.id;

-- Output:
-- username | bio
-- ---------+---------------------
-- mia      | Software developer
-- raj      | Marketing specialist
-- zoe      | NULL

INSERT INTO profiles (id, user_id, bio) VALUES (3, 1, 'Second profile');
-- ERROR: duplicate key value violates unique constraint "profiles_user_id_key"

Mitarbeitende und ihre Vorgesetzten

Eine 1:n-Beziehung innerhalb einer Tabelle: manager_id ist ein Fremdschlüssel auf employees.id. Joine die Tabelle mit sich selbst, um jeden Mitarbeiter neben seinen Vorgesetzten zu stellen:

-- Input: employees
-- id | name      | manager_id
-- ---+-----------+-----------
-- 1  | Maya Chen | NULL
-- 2  | Sam Ortiz | 1
-- 3  | Jo Kim    | 2
-- 4  | Lee Ross  | 2

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.id;

-- Output:
-- employee  | manager
-- ----------+----------
-- Maya Chen | NULL
-- Sam Ortiz | Maya Chen
-- Jo Kim    | Sam Ortiz
-- Lee Ross  | Sam Ortiz

Studierende und ihre Kurse

Die Verknüpfungstabelle enrollments hat einen Fremdschlüssel zu jeder Seite und PRIMARY KEY (student_id, course_id). Joine über sie, um aufzulisten, wer was belegt:

-- Input: students
-- id | name
-- ---+------
-- 1  | Priya
-- 2  | Tomas
-- 3  | Leah

-- Input: courses
-- id | title
-- ---+----------------
-- 1  | Database Design
-- 2  | Web Development
-- 3  | Statistics

-- Input: enrollments
-- student_id | course_id | grade
-- -----------+-----------+------
-- 1          | 1         | A
-- 1          | 2         | B+
-- 2          | 1         | A-
-- 3          | 2         | A

SELECT s.name AS student, c.title AS course, e.grade
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
ORDER BY s.name, c.title;

-- Output:
-- student | course          | grade
-- --------+-----------------+------
-- Leah    | Web Development | A
-- Priya   | Database Design | A
-- Priya   | Web Development | B+
-- Tomas   | Database Design | A-

INSERT INTO enrollments VALUES (1, 1, 'B');
-- ERROR: duplicate key value violates unique constraint "enrollments_pkey"

Kurse, in die sich niemand eingeschrieben hat

NOT EXISTS ist ein weiterer Weg, Zeilen ohne Treffer zu finden. Es behält jeden Kurs, der keine Zeile in der Verknüpfungstabelle hat:

-- Input: the courses and enrollments tables above

SELECT c.title
FROM courses c
WHERE NOT EXISTS (
  SELECT 1 FROM enrollments e WHERE e.course_id = c.id
);

-- Output:
-- title
-- ----------
-- Statistics

Weiterführende Artikel zu Datenbankbeziehungen

In diesem Blog:

• Leitfaden zu 1:1-Beziehungen: 1:1 im Detail
• Leitfaden zu 1:n-Beziehungen: Kindzeilen joinen, zählen und aggregieren
• Leitfaden zu n:m-Beziehungen: Verknüpfungstabellen im Detail
• SQL-JOINs mit Diagrammen: jeder Join-Typ, den du über eine Beziehung nutzt
• Fremdschlüssel in PostgreSQL nutzen: Fremdschlüssel-Optionen in PostgreSQL
• ON DELETE CASCADE in PostgreSQL: Kindzeilen zusammen mit ihrem Elternteil löschen
• Zusammengesetzte Primärschlüssel in SQL: der zweispaltige Schlüssel einer Verknüpfungstabelle
• Leitfaden zu SQL CREATE TABLE: Schlüssel und Constraints beim Anlegen einer Tabelle

Offizielle Dokumentation:

• PostgreSQL: Fremdschlüssel
• MySQL: FOREIGN-KEY-Constraints
• SQLite: Unterstützung für Fremdschlüssel
• SQL Server: Primär- und Fremdschlüssel-Constraints