Datenintegritätsprüfung

Heute schauen wir uns an, wie Foreign Keys uns helfen, die Datenintegrität zu überwachen und typische Probleme mit inkonsistenten oder falschen Daten zu vermeiden.

Als Erstes klären wir, was wir eigentlich mit "Datenintegrität" meinen. Stell dir vor, du hast eine Tabelle mit Bestellungen (orders) und eine Tabelle mit Kunden (customers). Wenn es eine Bestellung mit einem Kunden gibt, der nicht in der Kundentabelle steht, ist das ein Verstoß gegen die Integrität. Es ist wichtig, dass alle Daten in verbundenen Tabellen logisch zusammenpassen.

Datenintegrität bedeutet:

• Keine "leeren" Verweise: Wenn wir auf etwas in einer anderen Tabelle verweisen, dann existiert dieses "etwas" immer.
• Fehlertoleranz bei Änderungen: Wenn wir einen Wert aus einer Tabelle löschen, auf den andere Einträge verweisen, sollte uns die Datenbank warnen oder die Situation korrekt behandeln.

Genau dafür gibt es in PostgreSQL Foreign Keys.

Wie sorgen Foreign Keys für Datenintegrität?

Wenn ein Foreign Key in einer Tabelle angelegt wird, prüft PostgreSQL automatisch:

1. Vorhandensein von Daten in der Elterntabelle. Bevor ein Eintrag eingefügt oder aktualisiert wird, prüft PostgreSQL, ob der angegebene Foreign Key in der verknüpften Tabelle existiert.
2. Löschen oder Ändern von Daten. Bevor ein Eintrag in der Elterntabelle gelöscht oder geändert wird, prüft PostgreSQL, ob darauf noch Einträge in der Kindtabelle verweisen.

Foreign Keys sind sozusagen die "Wächter". Sie lassen keine falschen Daten durch und garantieren, dass Tabellen nur nach den festgelegten Regeln miteinander arbeiten.

Beispiel: Datenintegrität in Studenten- und Kurs-Tabellen

Nehmen wir an, wir haben zwei Tabellen — students und courses. Jeder Student kann sich für mehrere Kurse anmelden. Um diese Beziehung abzubilden, nutzen wir die Tabelle enrollments. Schauen wir uns an, was passiert, wenn jemand versucht, einen Studenten für einen nicht existierenden Kurs einzutragen.

Schritt 1. Wir erstellen drei verbundene Tabellen:

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),
    course_id INT REFERENCES courses(course_id)
);

Hier gilt:

• In der Tabelle enrollments haben wir explizit die Foreign Keys student_id und course_id angegeben, die auf die Primary Keys der Tabellen students und courses verweisen.

Typische Integritätsprüfungen

1. Prüfung beim Einfügen von Daten

Wenn wir versuchen, einen Eintrag in enrollments mit nicht existierendem student_id oder course_id einzufügen, gibt es einen Fehler.

Beispiel:

INSERT INTO enrollments (student_id, course_id)
VALUES (999, 1); -- Fehler! Student mit ID 999 existiert nicht.

Fehlermeldung:

ERROR:  insert or update on table "enrollments" violates foreign key constraint "enrollments_student_id_fkey"
DETAIL:  Key (student_id)=(999) is not present in table "students".

2. Prüfung beim Löschen von Daten

Wir versuchen, einen Eintrag aus der Elterntabelle zu löschen, auf den noch verwiesen wird.

Beispiel:

INSERT INTO students (name) VALUES ('Alice');
INSERT INTO courses (title) VALUES ('Mathematics');

INSERT INTO enrollments (student_id, course_id)
VALUES (1, 1); -- Erfolgreiches Einfügen

DELETE FROM students WHERE student_id = 1; -- Fehler, weil der Student noch für einen Kurs eingeschrieben ist!

Fehlermeldung:

ERROR:  update or delete on table "students" violates foreign key constraint "enrollments_student_id_fkey" on table "enrollments"
DETAIL:  Key (student_id)=(1) is still referenced from table "enrollments".

Um solche Einträge korrekt zu löschen, nutzen wir Strategien wie CASCADE, SET NULL oder RESTRICT, die wir schon besprochen haben.

Beispiele für den Einsatz von Foreign Keys zur Integritätsprüfung

Beispiel 1: Automatischer Schutz vor falschen Daten

Mit Foreign Keys verhindert PostgreSQL automatisch das Einfügen von "nicht existierenden" Daten:

-- Wir versuchen, nicht existierende Studenten in einen Kurs einzutragen:
INSERT INTO enrollments (student_id, course_id)
VALUES (42, 1); -- Fehler! Student mit ID 42 existiert nicht.

Das garantiert, dass ein Student sich nicht für einen Kurs anmelden kann, wenn er nicht in der students-Tabelle steht.

Beispiel 2: Löschen mit ON DELETE CASCADE

Wenn der Foreign Key auf ON DELETE CASCADE gesetzt ist, werden beim Löschen eines Eintrags in der Elterntabelle die verknüpften Daten in der Kindtabelle ebenfalls gelöscht.

ALTER TABLE enrollments DROP CONSTRAINT enrollments_student_id_fkey; -- Alten Foreign Key löschen

ALTER TABLE enrollments
ADD CONSTRAINT enrollments_student_id_fkey FOREIGN KEY (student_id)
REFERENCES students(student_id) ON DELETE CASCADE;

DELETE FROM students WHERE student_id = 1; -- Jetzt werden auch die Einträge in enrollments gelöscht

Beispiel 3: Änderungen mit ON UPDATE behandeln

Wenn der Foreign Key mit ON UPDATE CASCADE konfiguriert ist, aktualisiert PostgreSQL beim Ändern eines Wertes in der Elterntabelle automatisch die Daten in der Kindtabelle.

-- Wir konfigurieren den Foreign Key so, dass Änderungen am Elterntabellen-Key automatisch übernommen werden:
ALTER TABLE enrollments DROP CONSTRAINT enrollments_student_id_fkey;

ALTER TABLE enrollments
ADD CONSTRAINT enrollments_student_id_fkey FOREIGN KEY (student_id)
REFERENCES students(student_id) ON UPDATE CASCADE;

-- Wir ändern die student_id:
UPDATE students SET student_id = 10 WHERE student_id = 1;

-- Jetzt wird in enrollments die student_id auch auf 10 aktualisiert.

Datenintegrität testen

Es ist immer sinnvoll zu testen, wie sich die Foreign Key-Einstellungen in verschiedenen Szenarien verhalten:

1. Versuch, Daten mit falschem student_id oder course_id einzufügen.
2. Lösche Daten aus students und prüfe, wie sich die Tabelle enrollments verhält.
3. Ändere Daten in der Tabelle students und stelle sicher, dass die verknüpften Einträge aktualisiert werden.

Besonderheiten beim Arbeiten mit Foreign Keys

Manchmal gibt es Situationen, die verwirrend sein können:

• Kein Index vorhanden. Wenn die Elterntabelle (zum Beispiel students) keine indizierte Spalte hat, auf die verwiesen wird, kann PostgreSQL "versuchen", langsamer zu arbeiten. Deshalb sollte der Primary Key in der Elterntabelle immer ein Index sein.
• Zyklische Verweise. Wenn zwei Tabellen sich gegenseitig referenzieren, kann das beim Einfügen von Daten zu Problemen führen. In solchen Fällen muss man das Design besonders sorgfältig planen.
• Alle Einträge löschen. Wenn du alle Einträge mit Cascade-Löschung entfernen willst, musst du auf die Art der Daten in der Kindtabelle achten, um unerwartetes Verhalten zu vermeiden.

Um solche Probleme zu vermeiden, ist es wichtig, Tabellen sinnvoll zu entwerfen und die Beziehungsregeln zu testen, bevor sie in einer echten Datenbank angewendet werden.