Erstellen von Foreign Keys beim Anlegen von Tabellen

Willkommen zu einer der wichtigsten Vorlesungen in unserem Kurs! Heute quatschen wir darüber, wie man Foreign Keys in PostgreSQL erstellt. Das Thema ist mega wichtig beim Datenbank-Design, weil genau Foreign Keys die Verbindungen zwischen Tabellen ermöglichen. Falls du das Gefühl hast, du verläufst dich bald in deiner zukünftigen "SQL-Stadt", dann stell dir Foreign Keys wie Brücken vor, die verschiedene Stadtteile verbinden.

Ganz einfach gesagt: Ein Foreign Key ist eine Spalte (oder ein Set von Spalten) in einer Tabelle, die auf eine Spalte (meistens den Primary Key) einer anderen Tabelle verweist.

Zum Beispiel, wenn du zwei Tabellen hast – students (Studenten) und courses (Kurse), dann kann ein Foreign Key in der Tabelle courses "zeigen", auf welchen Studenten der Kurs gebucht ist. So entsteht die Verbindung zwischen diesen Tabellen.

Warum ist das wichtig?

1. Ein Foreign Key hilft, die Datenintegrität zu garantieren: Du kannst nichts in eine Tabelle eintragen, wenn es das nicht in der anderen gibt.
2. Sie machen die Arbeit mit Daten einfacher. Zum Beispiel kannst du beim Löschen eines Eintrags in einer Tabelle das automatische Löschen der verknüpften Einträge in einer anderen Tabelle einstellen.

Syntax zum Erstellen eines Foreign Keys

Einen Foreign Key in PostgreSQL zu erstellen ist easy – ein bisschen SQL-Magie reicht. Hier ist die Grundsyntax:

CREATE TABLE abhaengige_tabelle (
    spalte_foreign_id DATA_TYPE REFERENCES eltern_tabelle(spalte_id)
);

Lass uns lieber tiefer eintauchen und ein paar Beispiele anschauen.

Beispiel 1: Tabellen students und courses

Stell dir vor, wir wollen eine Verbindung zwischen Studenten und Kursen herstellen. Jeder Kurs soll mit irgendeinem Studenten verknüpft sein. Dafür reicht folgender Befehl:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    student_id INT REFERENCES students(student_id)
);

Hier:

• In der Tabelle students haben wir einen Primary Key PRIMARY KEY erstellt, um jeden Studenten zu identifizieren.
• In der Tabelle courses ist die Spalte student_id ein Foreign Key FOREIGN KEY, der auf die Spalte student_id in der Tabelle students verweist.

Tabelle students

Tabelle courses

Wichtiger Hinweis

Wenn du einen Foreign Key hinzufügst, erstellt PostgreSQL automatisch eine Regel, die prüft, ob die Werte im Foreign Key mit existierenden Werten in der angegebenen Tabelle übereinstimmen. Versuchst du, einen falschen Wert einzufügen, gibt die Datenbank einen Fehler aus.

Praktische Anwendung: Modell students, courses und enrollments

Schauen wir uns ein komplexeres Beispiel mit einer Many-to-Many-Beziehung an. Die gleichen Studenten können sich für mehrere Kurse anmelden, und ein Kurs kann von vielen Studenten besucht werden. Um so eine Verbindung zu bauen, brauchen wir eine Zwischentabelle.

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id), -- foreign key
    course_id INT REFERENCES courses(course_id)		-- foreign key
);

Hier verbindet die Tabelle enrollments die Tabellen students und courses mit den Foreign Keys student_id und course_id.

Daten einfügen

-- Studenten hinzufügen
INSERT INTO students (name) VALUES ('Ivan Ivanov'), ('Maria Smirnova');

-- Kurse hinzufügen
INSERT INTO courses (title) VALUES ('Mathematik'), ('Physik');

-- Studenten zu Kursen anmelden
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1), (1, 2), (2, 1);

Daten abfragen

Jetzt können wir easy Abfragen machen, um rauszufinden, welche Kurse ein Student besucht oder welche Studenten für einen bestimmten Kurs angemeldet sind:

-- Kurse, für die Ivan Ivanov angemeldet ist
SELECT c.title
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
WHERE e.student_id = 1;

-- Studenten, die für den Kurs "Mathematik" angemeldet sind
SELECT s.name
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
WHERE e.course_id = 1;

Erweiterte Möglichkeiten: ON DELETE und ON UPDATE

Ein Foreign Key sollte auch das Verhalten der Tabelle beim Ändern oder Löschen von Einträgen in der Elterntabelle steuern. Dafür gibt es die Modifikatoren ON DELETE und ON UPDATE. Hier die wichtigsten Optionen:

• CASCADE: Änderungen oder Löschungen in der Elterntabelle werden automatisch auf die abhängigen Einträge angewendet.
• SET NULL: Die Foreign Key-Werte in der abhängigen Tabelle werden auf NULL gesetzt.
• RESTRICT: Verhindert das Löschen oder Ändern von Daten, wenn sie schon in der abhängigen Tabelle verwendet werden.
• NO ACTION: Im Prinzip wie RESTRICT, aber die Prüfung passiert später.

Beispiel 2: Verwendung von ON DELETE CASCADE

Stell dir vor, wir wollen, dass beim Löschen eines Studenten aus der Tabelle students automatisch alle seine Kurse aus der Tabelle courses gelöscht werden. So geht's:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    student_id INT REFERENCES students(student_id) ON DELETE CASCADE
);

Jetzt, wenn du einen Studenten aus der Tabelle students löschst, werden alle Einträge, die mit diesem Studenten in der Tabelle courses verbunden sind, auch gelöscht. Zum Beispiel:

INSERT INTO students (name) VALUES ('Ivan Ivanov');
INSERT INTO courses (title, student_id) VALUES ('Mathematik', 1), ('Physik', 1);

-- Student Ivanov löschen
DELETE FROM students WHERE student_id = 1;

-- Die Tabelle courses ist jetzt leer, weil alle Kurse, die mit Ivanov verbunden waren, gelöscht wurden

Noch mal und ausführlicher besprechen wir das Thema in der nächsten Vorlesung.

Validierungsprozess von Daten bei Verwendung von Foreign Keys

Wenn du einen Foreign Key erstellst, übernimmt PostgreSQL die Rolle eines strengen Türstehers und prüft jeden neuen Eintrag. Zum Beispiel:

• Wenn du versuchst, einen Eintrag mit einem nicht existierenden Foreign Key-Wert einzufügen, bekommst du einen Fehler.
• Wenn du einen Eintrag in der Elterntabelle löschst, der mit anderen Tabellen verbunden ist (ohne ON DELETE CASCADE), führt das zu einem Verstoß gegen die Datenintegrität.

Beispiel: Versuch, falsche Daten einzufügen

-- Versuch, einen Kurs für einen nicht existierenden Studenten einzutragen
INSERT INTO enrollments (student_id, course_id) VALUES (3, 1);
-- Fehler: Verstoß gegen die Foreign Key-Bedingung

Typische Fehler beim Erstellen von Foreign Keys

1. Kein Index auf dem Foreign Key. PostgreSQL erstellt automatisch einen Index für den Primary Key, aber nicht für den Foreign Key. Wenn du den Foreign Key oft in WHERE-Bedingungen nutzt, solltest du den Index manuell anlegen.
2. Falsche Reihenfolge beim Erstellen der Tabellen. Du kannst keinen Foreign Key erstellen, der auf eine Tabelle verweist, die noch nicht existiert.
3. Modifikator ON DELETE oder ON UPDATE vergessen. Das kann zu unerwartetem Verhalten beim Bearbeiten von Daten führen.

Jetzt, wo du weißt, wie man Foreign Keys erstellt, hast du ein mächtiges Tool, um strukturierte und konsistente Datenbanken zu bauen. In der nächsten Vorlesung schauen wir uns ON DELETE CASCADE und ON UPDATE RESTRICT noch genauer an, um die Verwaltung von verbundenen Daten zu meistern.