Integritätsbedingungen in SQL: Ein Leitfaden mit Beispielen

Stell dir eine Krankenhausdatenbank vor, in der Allergien von Patienten nicht leer bleiben dürfen, oder ein Finanzsystem, in dem Transaktionsbeträge immer positiv sein müssen. In solchen und unzähligen anderen Szenarien sorgen Integritätsbedingungen dafür, dass Daten korrekt, konsistent und zuverlässig bleiben. 

In SQL sind Integritätsbedingungen Regeln, die wir für unsere Datenbanktabellen festlegen, um Datenqualität sicherzustellen. Sie verhindern Fehler, erzwingen fachliche Regeln und sorgen dafür, dass unsere Daten reale Objekte und Beziehungen korrekt abbilden.

In diesem Artikel schauen wir uns die wichtigsten Arten von Integritätsbedingungen in SQL an, erklären sie verständlich und zeigen praktische Beispiele in einer PostgreSQL-Datenbank. Auch wenn wir PostgreSQL-Syntax nutzen, lassen sich die Konzepte leicht auf andere SQL-Dialekte übertragen.

Wenn du mehr über SQL lernen möchtest, wirf einen Blick auf diese SQL-Kurse.

Was sind Integritätsbedingungen in SQL?

Stell dir eine Tabelle vor, die Nutzerdaten für eine Webanwendung speichert. Bestimmte Angaben wie das Alter können optional sein, weil sie die Nutzung der Anwendung nicht verhindern. Ein Passwort pro Nutzer ist für den Login jedoch unverzichtbar. Dafür würden wir eine Integritätsbedingung für die Passwortspalte der Nutzertabelle einrichten, damit jeder Eintrag ein Passwort enthält.

Kurz gesagt, Integritätsbedingungen sind entscheidend, um:

• Fehlende Daten zu verhindern.
• Sicherzustellen, dass Daten den erwarteten Typen und Wertebereichen entsprechen.
• Korrekte Verknüpfungen zwischen Daten in verschiedenen Tabellen zu erhalten.

In diesem Artikel behandeln wir folgende zentrale Integritätsbedingungen in SQL:

• PRIMARY KEY: Identifiziert jeden Datensatz in einer Tabelle eindeutig.
• NOT NULL: Stellt sicher, dass eine Spalte keine NULL-Werte enthält.
• UNIQUE: Gewährleistet, dass alle Werte in einer Spalte oder Spaltengruppe eindeutig sind.
• DEFAULT: Legt einen Standardwert für eine Spalte fest, wenn keiner angegeben wird.
• CHECK: Erzwingt, dass alle Werte in einer Spalte eine bestimmte Bedingung erfüllen.
• FOREIGN KEY: Stellt Beziehungen zwischen Tabellen her, indem auf einen Primary Key in einer anderen Tabelle verwiesen wird.

Fallstudie: Universitätsdatenbank

Betrachten wir eine relationale Datenbank für eine Universität. Sie enthält drei Tabellen: students, courses und enrollments.

Tabelle students

Die Tabelle students enthält Informationen über alle Studierenden der Universität.

• student_id: Die Kennung der Studentin bzw. des Studenten.
• first_name: Der Vorname.
• last_name: Der Nachname.
• email: Die E-Mail-Adresse.
• major: Das Studienfach.
• enrollment_year: Das Jahr der Immatrikulation.

Tabelle courses

Die Tabelle courses enthält Informationen über die an der Universität angebotenen Kurse.

• course_id: Die Kurskennung.
• course_name: Der Kursname.
• department: Die zugehörige Fakultät/Abteilung.

Tabelle enrollments

Die Tabelle enrollments speichert, welche Studierenden in welche Kurse eingeschrieben sind.

• student_id: Die Kennung der eingeschriebenen Studentin bzw. des eingeschriebenen Studenten.
• course_id: Die Kennung des Kurses.
• year: Das Einschreibejahr.
• grade: Die Note in diesem Kurs.
• is_passing_grade: Ein Boolean, der angibt, ob die Note bestanden ist.

Im weiteren Verlauf nutzen wir diese Beispieldatenbank und zeigen mehrere Wege, Datenintegrität durchzusetzen. Wir verwenden PostgreSQL-Syntax; die Konzepte lassen sich jedoch leicht auf andere SQL-Varianten übertragen.

PRIMARY KEY Constraint

Die Universität möchte jede Studentin und jeden Studenten eindeutig identifizieren. Vor- und Nachnamen sind dafür ungeeignet, weil Namen mehrfach vorkommen können. Auch E-Mail-Adressen sind keine gute Wahl, da sie geändert werden können.

Die übliche Lösung ist, jeder Person eine eindeutige Kennung zuzuweisen, die wir in der Spalte student_id speichern. Mit einer PRIMARY KEY-Bedingung auf student_id stellen wir sicher, dass jede Person eine eindeutige Kennung hat.

Die Bedingung wird im CREATE TABLE-Befehl nach dem Datentyp der Spalte definiert:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT,
    major TEXT,
    enrollment_year INT
);

Die obige Abfrage erstellt die Tabelle für Studierende mit den sechs genannten Spalten.

Die PRIMARY KEY-Bedingung stellt sicher, dass:

1. Jede Person eine student_id hat.
2. Jede student_id eindeutig ist.

PRIMARY KEY über mehrere Spalten

In manchen Fällen müssen mehrere Spalten kombiniert werden, um eine Zeile eindeutig zu identifizieren. Betrachte die Tabelle enrollments: Eine Person kann mehrere Kurse belegen, wodurch mehrere Zeilen dieselbe student_id haben. Umgekehrt hat ein Kurs viele Teilnehmende, sodass mehrere Zeilen dieselbe course_id teilen.

Da kein einzelnes Feld eine Zeile eindeutig identifiziert, bestimmen wir einen Einschreibedatensatz über die Kombination aus student_id, course_id und year.

Wenn mehrere Spalten beteiligt sind, wird der PRIMARY KEY am Ende des CREATE TABLE-Befehls angegeben.

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    year INT,
    grade INT,
    is_passing_grade BOOLEAN,
    PRIMARY KEY (student_id, course_id, year)
);

Integritätsbedingungen nachträglich hinzufügen

Es gibt zwei Möglichkeiten, Integritätsbedingungen hinzuzufügen. Wir haben gerade gesehen, wie das direkt bei der Tabellenerstellung geht.

Angenommen, die Tabelle existiert bereits, aber du hast die PRIMARY KEY-Bedingung vergessen. Dann kannst du sie nachträglich mit ALTER TABLE definieren, zum Beispiel so:

ALTER TABLE enrollments
ADD CONSTRAINT enroll_pk 
PRIMARY KEY (student_id, course_id, year);

In der ALTER TABLE-Anweisung haben wir die Bedingung enroll_pk genannt (für Enrollment Primary Key). Der Name kann ein beliebiger Bezeichner sein, sinnvoll ist jedoch ein Name, der den Zweck kurz und prägnant beschreibt.

Es ist Best Practice, Integritätsbedingungen zu benennen, denn das bietet mehrere Vorteile:

• Einfachere Referenz, insbesondere wenn die Bedingung später geändert oder gelöscht werden soll.
• Bessere Verwaltung und Übersicht, vor allem in Datenbanken mit vielen Bedingungen.

NOT NULL Constraint

Die Universität möchte sicherstellen, dass zu jedem Studierenden Name und E-Mail in der Datenbank erfasst werden. Das Personal soll diese Felder nicht versehentlich weglassen können.

Dazu setzen wir beim Erstellen der Tabelle NOT NULL-Bedingungen auf diese drei Spalten:

CREATE TABLE students (
    student_id INT,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT NOT NULL,
    major TEXT,
    enrollment_year INT
);

Die Abfrage oben verwendet NOT NULL, um sicherzustellen, dass die Spalten first_name, last_name und email keine NULL-Werte (undefiniert) enthalten können.

Eine NOT NULL-Bedingung kannst du auch nachträglich mit ALTER TABLE hinzufügen. Die Syntax dafür lautet:

ALTER TABLE students
ALTER COLUMN first_name SET NOT NULL,
ALTER COLUMN last_name SET NOT NULL,
ALTER COLUMN email SET NOT NULL;

UNIQUE Constraint

E-Mail-Adressen sind pro Person eindeutig. Wenn wir sicher sind, dass ein Feld niemals doppelte Werte enthalten darf, sollten wir das auf Datenbankebene erzwingen. So vermeiden wir Fehler und erhöhen die Datenintegrität.

Das Hinzufügen dieser Bedingung funktioniert analog zu den anderen.

CREATE TABLE students (
    ...
    email TEXT UNIQUE,
    ...
);

In unserem Fall wollen wir zusätzlich NOT NULL erzwingen. Mehrere Bedingungen auf einer Spalte werden einfach durch Leerzeichen kombiniert: 

CREATE TABLE students (
    ...
    email TEXT UNIQUE NOT NULL,
    ...
);

Die Reihenfolge spielt keine Rolle; NOT NULL UNIQUE wäre ebenso gültig.

Eine UNIQUE-Bedingung für eine bestehende Tabelle fügst du so hinzu:

ALTER TABLE students 
ADD CONSTRAINT unique_emails UNIQUE (email);

UNIQUE über mehrere Spalten

Angenommen, wir wollen sicherstellen, dass der Kursname pro Abteilung in der Tabelle courses eindeutig ist. Dann muss die Kombination aus course_name und department einzigartig sein.

Wenn mehrere Spalten beteiligt sind, fügen wir die Bedingung am Ende des CREATE TABLE-Befehls hinzu:

CREATE TABLE courses (
    course_id INT,
    course_name TEXT,
    department TEXT,
    UNIQUE (course_name, department)
);

Alternativ können wir die Bedingung per ALTER TABLE ergänzen. In diesem Fall geben wir die Spaltennamen als Tupel an:

ALTER TABLE courses 
ADD CONSTRAINT unique_course_name_department 
UNIQUE (course_name, department);

NOT NULL UNIQUE vs. PRIMARY KEY

Wir haben gelernt, dass ein PRIMARY KEY sowohl Eindeutigkeit als auch Nicht-Null sicherstellt. Worin liegt also der Unterschied zwischen:

course_id INT PRIMARY KEY

course_id INT UNIQUE NOT NULL

Der Unterschied zwischen NOT NULL UNIQUE und PRIMARY KEY liegt in Zweck und Einsatz. 

Beide erzwingen Eindeutigkeit und Nicht-Null, aber eine Tabelle kann nur einen PRIMARY KEY haben, der den Datensatz eindeutig identifiziert.

Die Kombination NOT NULL UNIQUE kann hingegen auf zusätzliche Spalten angewandt werden, um fachspezifische Regeln durchzusetzen: eindeutige Werte ohne NULL. Eine Tabelle kann beliebig viele NOT NULL UNIQUE-Bedingungen besitzen.

Beide Möglichkeiten erlauben eine flexible Gestaltung des Datenmodells: Wir können Eindeutigkeit und Integrität auf verschiedene Weise sicherstellen und zugleich zwischen dem primären Identifikator eines Datensatzes und weiteren wichtigen, eindeutigen Attributen unterscheiden.

DEFAULT Constraint

Nach der Einschreibung brauchen Studierende möglicherweise Zeit, um ihr Hauptfach zu wählen. Für diese Fälle soll in der Spalte major automatisch der Text „Undeclared“ gesetzt werden.

Dazu legen wir über die DEFAULT-Bedingung einen Standardwert fest. Wir passen die Tabelle students so an:

ALTER TABLE students
ALTER COLUMN major SET DEFAULT 'Undeclared';

Möchten wir die DEFAULT-Bedingung bereits bei der Tabellenerstellung setzen, deklarieren wir sie direkt nach dem Datentyp der Spalte:

CREATE TABLE students (
    ...
    major TEXT DEFAULT 'Undeclared',
    ...
);

CHECK Constraint

An dieser Universität reichen die Noten von 0 bis 100. Ohne Einschränkung akzeptiert die Spalte grade in der Tabelle enrollment jeden Integer-Wert. Mit einer CHECK-Bedingung können wir erzwingen, dass Werte zwischen 0 und 100 liegen.

ALTER TABLE enrollments
ADD CONSTRAINT grade_range CHECK (grade BETWEEN 0 AND 100);

Allgemein erlauben CHECK-Bedingungen, spezifische Anforderungen an die Daten zu validieren. Das ist wichtig für Konsistenz und Integrität.

Ein CHECK kann mehrere Spalten einbeziehen. Nutzen wir ihn, um konsistente Werte für grade und is_passing_grade zu erzwingen. Angenommen, ab 60 gilt eine Note als bestanden. Dann muss is_passing_grade genau dann TRUE sein, wenn die Note mindestens 60 beträgt. Wir zeigen das in der Tabellenerstellung, um die Deklaration in CREATE TABLE zu veranschaulichen:

CREATE TABLE enrollments (
    ...
    grade INT,
    is_passing_grade BOOLEAN,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    )
);

Hier gibt es ein Problem: Bei der Einschreibung liegt noch keine Note vor. grade sollte also NULL sein können. Dann müssen wir auch die Bedingung für die Bestanden-Logik so anpassen, dass is_passing_grade ebenfalls NULL sein darf, solange es keine Note gibt. So aktualisieren wir die Bedingung:

CREATE TABLE enrollments (
    ...    
    grade INT NULL DEFAULT NULL,
    is_passing_grade BOOLEAN NULL DEFAULT NULL,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade IS NULL AND is_passing_grade IS NULL) OR
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    ),
    ...
);

Beachte, dass wir bei grade und is_passing_grade jeweils NULL am Datentyp und als Standardwert angegeben haben. Das dient in erster Linie der besseren Lesbarkeit.

Schauen wir uns nun an, welche Bedingungen sich mit CHECK durchsetzen lassen.

Bereichsbedingungen

Wir können sicherstellen, dass Werte in einer Spalte in einem bestimmten Bereich liegen.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name BETWEEN min_value AND max_value);

Listenbedingungen

Wir können prüfen, ob der Spaltenwert in einer vorgegebenen Werteliste enthalten ist.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name IN ('Value1', 'Value2', 'Value3'));

Vergleichsbedingungen

Wir können Werte einer Spalte vergleichen, um Bedingungen wie größer als, kleiner als etc. zu erfüllen.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name > some_value);

Musterabgleich

Wir können Musterabgleiche (z. B. mit LIKE oder SIMILAR TO) verwenden, um Textdaten zu validieren.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column_name LIKE 'pattern');

Logische Bedingungen

Wir können mehrere Bedingungen mit logischen Operatoren (AND, OR, NOT) kombinieren.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (condition1 AND condition2 OR condition3);

Zusammengesetzte Bedingungen

Wendet eine Bedingung über mehrere Spalten an.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
CHECK (column1 + column2 < some_value);

FOREIGN KEY Constraint

Fremdschlüsselbedingungen verknüpfen Spalten zweier Tabellen und sichern so die referenzielle Integrität. Ein Fremdschlüssel in einer Tabelle verweist auf einen Primary Key in einer anderen Tabelle und zeigt damit an, dass die Zeilen beider Tabellen miteinander in Beziehung stehen. So wird verhindert, dass es in einer Tabelle eine Zeile mit einem Fremdschlüssel gibt, der auf keinen Datensatz in der referenzierten Tabelle verweist.

In unserem Beispiel verweist jeder Datensatz in enrollments über student_id und course_id auf eine Person und einen Kurs. Ohne Einschränkung gibt es nichts, was sicherstellt, dass diese Kennungen in enrollments auch wirklich existierenden Einträgen in students und courses entsprechen.

So stellen wir das bei der Erstellung von enrollments sicher:

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    ...
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

Wichtig: Damit dies Fremdschlüssel sind, müssen die referenzierten Spalten in students bzw. courses jeweils Primary Keys sein.

Wie bei anderen Bedingungen können wir sie auch nachträglich mit ALTER TABLE hinzufügen:

ALTER TABLE enrollments
ADD CONSTRAINT fk_student_id
FOREIGN KEY (student_id)
REFERENCES students(student_id);

ALTER TABLE enrollments
ADD CONSTRAINT fk_course_id
FOREIGN KEY (course_id)
REFERENCES courses(course_id);

Alles zusammengefügt

Im Artikel haben wir verschiedene Integritätsbedingungen vorgestellt und gezeigt, wie sie eine Universitätsdatenbank verbessern. Hier ist die finale Version der CREATE TABLE-Befehle, die alles kombiniert.

Zuerst definieren wir die Tabelle students:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    major TEXT DEFAULT 'Undeclared',
    enrollment_year INT,
    CONSTRAINT year_check CHECK (enrollment_year >= 1900),
    CHECK (major IN (
        'Undeclared',
        'Computer Science',
        'Mathematics',
        'Biology',
        'Physics',
        'Chemistry',
        'Biochemistry'
    ))
);

Als Nächstes definieren wir die Tabelle courses:

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name TEXT NOT NULL,
    department TEXT NOT NULL,
    UNIQUE (course_name, department),
    CHECK (department IN (
        'Physics & Mathematics',
        'Sciences'
    ))
);

Zum Schluss definieren wir die Tabelle enrollments und stellen die Beziehungen zwischen Studierenden und Kursen her:

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    year INT CHECK (year >= 1900),
    grade INT NULL DEFAULT NULL,
    is_passing_grade BOOLEAN NULL DEFAULT NULL,
    CONSTRAINT grade_check CHECK (grade BETWEEN 0 AND 100),
    CONSTRAINT is_passing_grade CHECK (
        (grade IS NULL AND is_passing_grade IS NULL) OR
        (grade >= 60 AND is_passing_grade = TRUE) OR
        (grade < 60 AND is_passing_grade = FALSE)
    ),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id, year)
);

In diesem finalen Beispiel haben wir einige weitere Bedingungen hinzugefügt. Eine Universität hat eine bekannte Menge an Abteilungen und Studienfächern. Daher ist die Menge der zulässigen Werte für die Spalten major und department endlich und im Voraus bekannt. In solchen Fällen empfiehlt sich eine CHECK-Bedingung, um sicherzustellen, dass die Spalten nur Werte aus diesem definierten Set annehmen.

Fazit

In diesem Artikel haben wir die verschiedenen Typen von Integritätsbedingungen in SQL beleuchtet und ihre Implementierung in PostgreSQL gezeigt. Wir haben Primary Keys, NOT NULL-, UNIQUE-, DEFAULT-, CHECK- und FOREIGN KEY-Bedingungen behandelt und jeweils praxisnahe Beispiele gegeben.

Wenn wir diese Konzepte verstehen, können wir die Genauigkeit, Konsistenz und Zuverlässigkeit unserer Daten sicherstellen.

Wenn du mehr darüber lernen willst, Daten effizient zu organisieren, schau dir diesen Kurs zu Database Design an.