Zum Inhalt springen
aviral gupta
Kontakt aufnehmen

// B4.3 · ca. 30 Min. · Einstieg

Constraints: Primärschlüssel, Fremdschlüssel, UNIQUE, CHECK

Nach dieser Lektion geben Sie einer Tabelle Regeln, die fehlerhafte Zeilen ablehnen, verknüpfen Ausleihen mit echten Büchern und Mitgliedern, legen fest, was ein Löschen mit ihnen macht, und lesen den Fehler einer verletzten Regel.

Lektion 3 von 5 in B4 Daten und Schema ändern

Danach können Sie

  • NOT NULL, UNIQUE, PRIMARY KEY und CHECK deklarieren und vorhersagen, welche Zeilen sie ablehnen
  • Tabellen mit REFERENCES verknüpfen und wählen, was ON DELETE tut: NO ACTION, RESTRICT oder CASCADE
  • Einen Constraint-Fehler lesen und die Regel und die Zeile dahinter finden

  1. Aufwärmen · Aufgabe 1 von 7

    Aufwärmen: Zwei Mitglieder dürfen nie dieselbe E-Mail-Adresse haben. Welcher Constraint sagt das?

  2. Vorhersagen · Aufgabe 2 von 7

    Sagen Sie es voraus, bevor Sie weiterlesen. Was passiert beim zweiten INSERT?

    CREATE TABLE members (id integer PRIMARY KEY, name text);
INSERT INTO members VALUES (1, 'Ada');
INSERT INTO members VALUES (1, 'Grace');

  3. Üben · Aufgabe 3 von 7

    Ergänzen Sie den Operator, damit copies 0 oder mehr sein darf, aber nie negativ.

    CREATE TABLE books (title text, copies integer CHECK (copies ____ 0));
    CREATE TABLE books (title text, copies integer CHECK (copies 0));

  4. Üben · Aufgabe 4 von 7

    Ordnen Sie jedem Constraint die Zeile zu, die er ablehnt.

  5. Üben · Aufgabe 5 von 7

    In der Bibliothek aus dem durchgerechneten Beispiel hat loans.member_id ON DELETE CASCADE. Von den fünf Ausleihen gehören zwei Grace (Mitglied 2). Wie viele Ausleihen bleiben?

    DELETE FROM members WHERE id = 2;

  6. Denksport · Aufgabe 6 von 7

    Knobelaufgabe. loans hat CHECK (returned_on >= loaned_on) und fünf Zeilen. Die neue Ausleihe hat noch kein Rückgabedatum. Was gibt das SELECT zurück?

    INSERT INTO loans VALUES (6, 4, 4, '2026-10-03', NULL);
SELECT count(*) FROM loans;

  7. Anwenden · Aufgabe 7 von 7

    Mini-Aufgabe. Erstellen Sie mit der Bibliothek mit Schlüsseln aus dem durchgerechneten Beispiel reviews: eine id, die jede Rezension identifiziert, das Buch und das Mitglied (beide Pflicht, und beide müssen existieren; wird ein Buch oder Mitglied gelöscht, gehen seine Rezensionen mit) und stars von 1 bis 5, Pflicht. Ein Mitglied darf ein Buch nur einmal bewerten. Fügen Sie zwei Rezensionen ein, versuchen Sie dann eine dritte desselben Mitglieds zum selben Buch und stars = 6, und lesen Sie beide Fehler.

    Prüfen Sie Ihr Ergebnis anhand dieser Liste

Selbst programmieren

Lesen Sie das ausgearbeitete Beispiel und lösen Sie dann die Übungen. Ihr Code läuft in Ihrem Browser oder auf Ihrem Computer und wird nie hochgeladen.

Ausgearbeitetes Beispiel

Die Bibliothek mit Schlüsseln und Regeln

main.sql baut die Bibliothek aus Modul B1 noch einmal, diesmal mit Regeln: Schlüssel auf jeder id, NOT NULL, wo ein Wert Pflicht ist, eine eindeutige E-Mail, CHECKs auf copies und den Datumswerten und Fremdschlüssel von loans zu books und members. Es listet die Constraints von loans mit den Namen auf, die PostgreSQL ihnen gegeben hat, löscht dann Linus und zählt die Ausleihen. Auf Ihrem Computer: psql -X -q -v ON_ERROR_STOP=1 -f main.sql.

main.sql

-- Referenced tables first: loans refers to both.
CREATE TABLE members (
  id integer PRIMARY KEY,
  name text NOT NULL,
  email text UNIQUE,
  city text,
  joined date NOT NULL
);

CREATE TABLE books (
  id integer PRIMARY KEY,
  title text NOT NULL,
  author text,
  year integer,
  genre text,
  copies integer NOT NULL CHECK (copies >= 0)
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

-- Every loan must point to an existing book and member.
CREATE TABLE loans (
  id integer PRIMARY KEY,
  book_id integer NOT NULL REFERENCES books (id),
  member_id integer NOT NULL REFERENCES members (id) ON DELETE CASCADE,
  loaned_on date NOT NULL,
  returned_on date,
  CHECK (returned_on >= loaned_on)
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

-- The rules of loans and their names (p primary key, f foreign key, c check, n not null).
SELECT conname, contype
FROM pg_constraint
WHERE conrelid = 'loans'::regclass
ORDER BY conname;

-- ON DELETE CASCADE: Linus leaves, and his loan goes with him.
DELETE FROM members WHERE id = 3;
SELECT count(*) AS loans_left FROM loans;

Ausführen mit

psql -X -q -v ON_ERROR_STOP=1 -f main.sql

Ausgabe

         conname          | contype
--------------------------+---------
 loans_book_id_fkey       | f
 loans_book_id_not_null   | n
 loans_check              | c
 loans_id_not_null        | n
 loans_loaned_on_not_null | n
 loans_member_id_fkey     | f
 loans_member_id_not_null | n
 loans_pkey               | p
(8 rows)

 loans_left
------------
          4
(1 row)
  • Der CHECK über zwei Spalten wurde loans_check genannt; die anderen tragen ihren Spaltennamen.
  • Der Primärschlüssel hat id auch NOT NULL gemacht: loans_id_not_null.
  • Jede Zeile der Daten hat jede Regel bestanden, also liefen alle INSERTs.
  • Das Löschen von Mitglied 3 hat auch Ausleihe 4 gelöscht, die einzige, die auf ihn verwies.
Ändern und ausführen

Tab rückt ein, Umschalt+Tab rückt aus. Um den Editor mit der Tastatur zu verlassen, drücken Sie Esc und dann Tab.

Beim ersten Ausführen lädt Ihr Browser PostgreSQL herunter (bis zu 10.1 MB) und speichert es im Cache. Jeder Lauf beginnt mit einer leeren Datenbank. Ihr Code bleibt auf Ihrem Gerät.

Übungen

Übung 1 von 2

Geben Sie books seine Regeln

Der Startcode legt books nur mit Typen an, also nähme PostgreSQL ein Buch ohne Titel oder mit -5 Exemplaren an. Ergänzen Sie Regeln in CREATE TABLE: id ist der Primärschlüssel, title darf nicht NULL sein, und copies darf nicht NULL sein und muss 0 oder mehr sein (ein CHECK). Behalten Sie die sechs eingefügten Bücher.

Tab rückt ein, Umschalt+Tab rückt aus. Um den Editor mit der Tastatur zu verlassen, drücken Sie Esc und dann Tab.

Beim ersten Ausführen lädt Ihr Browser PostgreSQL herunter (bis zu 10.1 MB) und speichert es im Cache. Jeder Lauf beginnt mit einer leeren Datenbank. Ihr Code bleibt auf Ihrem Gerät.

Hinweise
  1. Hinweis 1

    Constraints stehen nach dem Typ ihrer Spalte: id integer PRIMARY KEY.

  2. Hinweis 2

    Eine Spalte kann zwei haben: copies integer NOT NULL CHECK (…).

  3. Hinweis 3

    Die Bedingung für copies ist copies >= 0, in Klammern nach CHECK.

Eine Lösung zeigen

Ein möglicher Lösungsweg. Ihrer kann anders aussehen und trotzdem alle Prüfungen bestehen.

CREATE TABLE books (
  id integer PRIMARY KEY,
  title text NOT NULL,
  author text,
  year integer,
  genre text,
  copies integer NOT NULL CHECK (copies >= 0)
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);
Auf dem eigenen Computer ausführen

Installieren Sie PostgreSQL 18 oder neuer. Speichern Sie diese Dateien in einem Ordner, öffnen Sie dort ein Terminal und führen Sie die Befehle unten aus.

main.sql

-- Types only: PostgreSQL checks nothing else yet.
CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

test.sql

-- test: id ist der Primärschlüssel von books
SELECT EXISTS (SELECT 1 FROM pg_constraint c JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey) WHERE c.conrelid = 'books'::regclass AND c.contype = 'p' AND cardinality(c.conkey) = 1 AND a.attname = 'id');

-- test: title ist NOT NULL
SELECT attnotnull FROM pg_attribute WHERE attrelid = 'books'::regclass AND attname = 'title';

-- test: copies ist NOT NULL
SELECT attnotnull FROM pg_attribute WHERE attrelid = 'books'::regclass AND attname = 'copies';

-- test: Ein CHECK-Constraint schützt copies
SELECT EXISTS (SELECT 1 FROM pg_constraint c JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey) WHERE c.conrelid = 'books'::regclass AND c.contype = 'c' AND a.attname = 'copies');

-- test: Die sechs Bücher sind in der Tabelle
SELECT count(*) = 6 FROM books;

psql führt die Dateien auf Ihrem eigenen PostgreSQL-Server aus. Nehmen Sie eine leere Datenbank, die Sie wegwerfen können: Hängen Sie -d und ihren Namen an den Befehl an.

Programm ausführen:

psql -X -q -v ON_ERROR_STOP=1 -f main.sql

Für die Prüfungen gibt es auf Ihrem Computer noch keinen Befehl. Sie stehen in test.sql: Jede Prüfabfrage gibt t aus, wenn sie besteht.

Übung 2 von 2

Ausleihen, die auf echte Zeilen zeigen

setup.sql hat members und books mit ihren Schlüsseln angelegt. Der Startcode legt loans ohne Regeln an, dann verlässt Linus (Mitglied 3) die Bibliothek, und seine Ausleihe bleibt zurück und zeigt auf niemanden. Geben Sie loans seine Regeln: id ist der Primärschlüssel, book_id verweist auf books (id), member_id verweist auf members (id) mit ON DELETE CASCADE, und ein CHECK stellt sicher, dass returned_on nicht vor loaned_on liegt. Dann geht die Ausleihe von Linus mit ihm.

Tab rückt ein, Umschalt+Tab rückt aus. Um den Editor mit der Tastatur zu verlassen, drücken Sie Esc und dann Tab.

Beim ersten Ausführen lädt Ihr Browser PostgreSQL herunter (bis zu 10.1 MB) und speichert es im Cache. Jeder Lauf beginnt mit einer leeren Datenbank. Ihr Code bleibt auf Ihrem Gerät.

Hinweise
  1. Hinweis 1

    Ein Fremdschlüssel steht nach dem Spaltentyp: book_id integer REFERENCES books (id).

  2. Hinweis 2

    ON DELETE CASCADE folgt auf den REFERENCES-Teil von member_id.

  3. Hinweis 3

    Ein CHECK über zwei Spalten steht nach der letzten Spalte: , CHECK (returned_on >= loaned_on)

Eine Lösung zeigen

Ein möglicher Lösungsweg. Ihrer kann anders aussehen und trotzdem alle Prüfungen bestehen.

CREATE TABLE loans (
  id integer PRIMARY KEY,
  book_id integer REFERENCES books (id),
  member_id integer REFERENCES members (id) ON DELETE CASCADE,
  loaned_on date,
  returned_on date,
  CHECK (returned_on >= loaned_on)
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

DELETE FROM members WHERE id = 3;

SELECT id, book_id, member_id FROM loans ORDER BY id;
Auf dem eigenen Computer ausführen

Installieren Sie PostgreSQL 18 oder neuer. Speichern Sie diese Dateien in einem Ordner, öffnen Sie dort ein Terminal und führen Sie die Befehle unten aus.

main.sql

-- loans without rules, then Linus leaves the library.
CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

DELETE FROM members WHERE id = 3;

SELECT id, book_id, member_id FROM loans ORDER BY id;

test.sql

-- test: id ist der Primärschlüssel von loans
SELECT EXISTS (SELECT 1 FROM pg_constraint c JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey) WHERE c.conrelid = 'loans'::regclass AND c.contype = 'p' AND cardinality(c.conkey) = 1 AND a.attname = 'id');

-- test: book_id verweist auf books
SELECT EXISTS (SELECT 1 FROM pg_constraint WHERE conrelid = 'loans'::regclass AND contype = 'f' AND confrelid = 'books'::regclass);

-- test: member_id verweist auf members, mit ON DELETE CASCADE
SELECT EXISTS (SELECT 1 FROM pg_constraint WHERE conrelid = 'loans'::regclass AND contype = 'f' AND confrelid = 'members'::regclass AND confdeltype = 'c');

-- test: Ein CHECK-Constraint vergleicht die Datumswerte
SELECT EXISTS (SELECT 1 FROM pg_constraint WHERE conrelid = 'loans'::regclass AND contype = 'c');

-- test: Die Ausleihe von Linus ist mit ihm weg, die anderen vier bleiben
-- output:
--  id | book_id | member_id
-- ----+---------+-----------
--   1 |       1 |         1
--   2 |       3 |         1
--   3 |       2 |         2
--   5 |       6 |         2
-- (4 rows)

setup.sql

CREATE TABLE members (
  id integer PRIMARY KEY,
  name text NOT NULL,
  email text UNIQUE,
  city text,
  joined date NOT NULL
);

CREATE TABLE books (
  id integer PRIMARY KEY,
  title text NOT NULL,
  author text,
  year integer,
  genre text,
  copies integer NOT NULL CHECK (copies >= 0)
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

psql führt die Dateien auf Ihrem eigenen PostgreSQL-Server aus. Nehmen Sie eine leere Datenbank, die Sie wegwerfen können: Hängen Sie -d und ihren Namen an den Befehl an.

Programm ausführen:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Für die Prüfungen gibt es auf Ihrem Computer noch keinen Befehl. Sie stehen in test.sql: Jede Prüfabfrage gibt t aus, wenn sie besteht.

Häufige Fehler

Eine zweite Zeile mit demselben Schlüssel

INSERT INTO books VALUES (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2);

Was psql ausgibt

ERROR:  duplicate key value violates unique constraint "books_pkey"

Warum, und die Lösung

books hat schon eine Zeile mit id 1, und der Primärschlüssel erlaubt jede id nur einmal; die DETAIL-Zeile nennt den Schlüssel, (id)=(1). Die übliche Ursache ist ein Datenskript, das zweimal läuft. Geben Sie der neuen Zeile eine id, die keine andere Zeile hat, oder lassen Sie das INSERT weg, wenn die Zeile schon da ist, bzw. ändern Sie sie mit UPDATE.

Eine Ausleihe für ein Mitglied, das es nicht gibt

INSERT INTO loans VALUES (6, 1, 9, '2026-10-03', NULL);

Was psql ausgibt

ERROR:  insert or update on table "loans" violates foreign key constraint "loans_member_id_fkey"

Warum, und die Lösung

member_id REFERENCES members (id), und kein Mitglied hat die id 9 (DETAIL: Key (member_id)=(9) is not present in table "members"). Legen Sie zuerst das Mitglied an, dann die Ausleihe, oder verwenden Sie die id eines vorhandenen Mitglieds. Aus demselben Grund zählt die Reihenfolge beim Laden von Daten: Eltern vor Kindern.

Ein Buch löschen, das noch verliehen ist

DELETE FROM books WHERE id = 1;

Was psql ausgibt

ERROR:  update or delete on table "books" violates foreign key constraint "loans_book_id_fkey" on table "loans"

Warum, und die Lösung

Zwei Ausleihen verweisen noch auf Dune, und book_id hat die Standardaktion NO ACTION, also wird das Löschen abgelehnt. Löschen oder ändern Sie zuerst diese Ausleihen, oder deklarieren Sie REFERENCES books (id) ON DELETE CASCADE, wenn die Ausleihen mit dem Buch verschwinden sollen. RESTRICT lehnte es ebenfalls ab, mit den Worten violates RESTRICT setting of foreign key constraint.

PostgreSQL im Browser: PGlite 0.5.8 (PostgreSQL 18.3), Apache-2.0 und PostgreSQL License. Lizenz und Quellcode

Abschlussquiz

5 Fragen, ohne Hinweise. Ab 80 % ist die Lektion abgeschlossen.

Erledigen Sie zuerst alle Aufgaben oben, um das Abschlussquiz freizuschalten.

Problem melden

Etwas ist falsch oder unklar? Beschreiben Sie es kurz, dann wird es geprüft und korrigiert.

#

Mindestens 20 Zeichen.

Nur, wenn Sie eine Antwort wünschen.

Kernideen

Regeln innerhalb einer Tabelle

Ein Constraint ist eine Regel, die die Tabelle bei jedem INSERT und UPDATE prüft. Eine Zeile, die sie verletzt, wird mit einem Fehler abgelehnt, und die ganze Anweisung ändert nichts. NOT NULL verbietet einen fehlenden Wert; ein leerer String '' ist trotzdem ein Wert. UNIQUE verbietet zwei Zeilen mit demselben Wert, aber zwei NULLs gelten nicht als gleich, also dürfen viele Mitglieder keine E-Mail haben. PRIMARY KEY markiert die Spalte, die eine Zeile identifiziert: eindeutig und nicht NULL zugleich, und eine Tabelle hat höchstens einen. CHECK (copies >= 0) akzeptiert eine Zeile, wenn die Bedingung wahr oder NULL ist; ergänzen Sie also NOT NULL, wo auch ein fehlender Wert falsch ist.

Fremdschlüssel verknüpfen Tabellen

book_id integer REFERENCES books (id) besagt, dass jede book_id in loans die id eines vorhandenen Buchs sein muss. Die referenzierte Spalte muss ein Primärschlüssel oder UNIQUE sein, und ihre Tabelle muss zuerst existieren. Eine Ausleihe mit unbekannter book_id wird abgelehnt; eine NULL-book_id kommt durch, außer die Spalte ist NOT NULL. Die Regel schützt auch die andere Seite. Standardmäßig (NO ACTION) scheitert das Löschen eines Buchs, auf das noch Ausleihen verweisen. ON DELETE RESTRICT lehnt es ebenfalls ab, etwas strenger: Die Prüfung lässt sich nicht ans Ende einer Transaktion verschieben. ON DELETE CASCADE löscht stattdessen die verweisenden Zeilen: Löschen Sie ein Mitglied, gehen seine Ausleihen mit.

Einen Constraint-Fehler lesen

Jeder Constraint hat einen Namen, und die Fehlermeldung nennt ihn. Ohne CONSTRAINT name bildet PostgreSQL einen aus Tabelle und Spalte: books_pkey, members_email_key für UNIQUE, books_copies_check, loans_member_id_fkey und loans_check für einen CHECK, der nach den Spalten steht. Die Meldungen lauten: duplicate key value violates unique constraint "books_pkey"; null value in column "title" of relation "books" violates not-null constraint; new row for relation "books" violates check constraint "books_copies_check"; insert or update on table "loans" violates foreign key constraint "loans_member_id_fkey". Die DETAIL-Zeile darunter zeigt den Schlüssel oder die fehlerhafte Zeile.

Quellen

Zuletzt geprüft am 3. Oktober 2026