Zum Inhalt springen
aviral gupta
Kontakt aufnehmen

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

Projekt: das Bibliotheksschema weiterentwickeln

Nach diesem Projekt legen Sie Regeln auf eine Datenbank, die schon fehlerhafte Daten enthält: die verletzenden Zeilen finden, korrigieren, die Regeln hinzufügen und zeigen, dass sie gelten.

Lektion 5 von 5 in B4 Daten und Schema ändern

Ende des Moduls

Danach können Sie

  • Die Zeilen finden, die eine neue Regel verletzen würden, und sie mit UPDATE und DELETE korrigieren
  • Schlüssel, Fremdschlüssel, einen CHECK, ein NOT NULL und einen Standardwert in einer Reihenfolge hinzufügen, die funktioniert
  • Die Regeln beweisen: vorhersagen, welche INSERTs und DELETEs das weiterentwickelte Schema jetzt ablehnt

  1. Aufwärmen · Aufgabe 1 von 7

    Aufwärmen aus der letzten Lektion. Persuasion wurde mit copies NULL eingefügt. Was passiert?

    ALTER TABLE books ALTER COLUMN copies SET NOT NULL

  2. Vorhersagen · Aufgabe 2 von 7

    Sagen Sie es voraus, bevor Sie weiterlesen. Die Bibliothek hat Ausleihe 6 für ein Mitglied 9, das es nicht gibt, Ausleihe 7, die vor dem Verleih zurückkam, und Persuasion ohne Exemplare. Noch keine Tabelle hat einen Schlüssel. Welche dieser Regeln lässt sich sofort hinzufügen?

  3. Üben · Aufgabe 3 von 7

    Ergänzen Sie die Lücke, damit die Abfrage die Ausleihen auflistet, deren Mitglied nicht existiert.

    SELECT l.id, l.member_id
FROM loans l
LEFT JOIN members m ON m.id = l.member_id
WHERE m.id IS ____;
    SELECT l.id, l.member_id FROM loans l LEFT JOIN members m ON m.id = l.member_id WHERE m.id IS ;

  4. Üben · Aufgabe 4 von 7

    Bringen Sie die Schritte des Projekts in eine Reihenfolge, die auf der Bibliothek mit ihren fehlerhaften Zeilen funktioniert.

    1. 1.Die Zeilen finden, die die Regeln verletzen würden
    2. 2.Die Fremdschlüssel, den CHECK und das NOT NULL hinzufügen
    3. 3.Sie mit UPDATE und DELETE korrigieren
    4. 4.Die Primärschlüssel hinzufügen
    5. 5.Ein fehlerhaftes INSERT versuchen und den Fehler lesen

  5. Üben · Aufgabe 5 von 7

    Die neue Regel wird CHECK (returned_on >= loaned_on). SELECT id FROM loans WHERE … Welche Bedingung listet genau die Ausleihen auf, die sie verletzen würden?

  6. Denksport · Aufgabe 6 von 7

    Knobelaufgabe. Die Daten sind jetzt korrigiert, aber die Ausleihen 2, 4 und 5 sind noch offen: Ihr returned_on ist NULL. Was passiert?

    ALTER TABLE loans ADD CHECK (returned_on >= loaned_on)

  7. Anwenden · Aufgabe 7 von 7

    Mini-Aufgabe. Im Editor des durchgerechneten Beispiels, nach dem Projekt: Fügen Sie Middlemarch von George Eliot als Buch 8 ohne copies hinzu und zeigen Sie seine copies. Versuchen Sie dann, als eigene Anweisung, eine Ausleihe 8 von Dune für Mitglied 9 und lesen Sie den Fehler. Welchen Constraint nennt er?

    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

Von fehlerhaften Zeilen zu durchgesetzten Regeln

setup.sql lädt die Bibliothek aus Modul B1 ohne jede Regel, dazu drei Zeilen, die hineingeraten sind: eine Ausleihe für Mitglied 9, eine Ausleihe mit vertauschten Daten und ein Buch ohne Exemplare. main.sql findet sie, korrigiert sie, fügt Schlüssel, Fremdschlüssel, einen CHECK, ein NOT NULL, einen Standardwert und eine neue Spalte hinzu und listet auf, was loans jetzt durchsetzt. Auf Ihrem Computer: psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql.

main.sql

-- 1. Find the rows that would break the new rules.
SELECT l.id, l.member_id
FROM loans l
LEFT JOIN members m ON m.id = l.member_id
WHERE m.id IS NULL;

SELECT id, loaned_on, returned_on FROM loans WHERE returned_on < loaned_on;

SELECT id, title FROM books WHERE copies IS NULL;

-- 2. Fix them.
DELETE FROM loans WHERE id = 6;
UPDATE loans SET loaned_on = '2026-09-15', returned_on = '2026-09-25' WHERE id = 7;
UPDATE books SET copies = 1 WHERE id = 7;

-- 3. Keys first, then the foreign keys and rules that rely on them.
ALTER TABLE books ADD PRIMARY KEY (id);
ALTER TABLE members ADD PRIMARY KEY (id);
ALTER TABLE loans ADD PRIMARY KEY (id);
ALTER TABLE loans ADD FOREIGN KEY (book_id) REFERENCES books (id);
ALTER TABLE loans ADD FOREIGN KEY (member_id) REFERENCES members (id);
ALTER TABLE loans ADD CHECK (returned_on >= loaned_on);
ALTER TABLE books ALTER COLUMN copies SET NOT NULL;
ALTER TABLE books ALTER COLUMN copies SET DEFAULT 1;

-- 4. A new column for members.
ALTER TABLE members ADD COLUMN active boolean NOT NULL DEFAULT true;

-- 5. What loans now enforces.
SELECT contype, count(*) FROM pg_constraint
WHERE conrelid = 'loans'::regclass
GROUP BY contype
ORDER BY contype;

setup.sql

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);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

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');

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);

-- Rows that got in while nothing was checked.
INSERT INTO books VALUES (7, 'Persuasion', 'Jane Austen', 1817, 'classic', NULL);
INSERT INTO loans VALUES
  (6, 5, 9, '2026-09-20', NULL),
  (7, 4, 4, '2026-09-25', '2026-09-15');

Ausführen mit

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

Ausgabe

 id | member_id
----+-----------
  6 |         9
(1 row)

 id | loaned_on  | returned_on
----+------------+-------------
  7 | 2026-09-25 | 2026-09-15
(1 row)

 id |   title
----+------------
  7 | Persuasion
(1 row)

 contype | count
---------+-------
 c       |     1
 f       |     2
 n       |     1
 p       |     1
(4 rows)
  • Die drei Abfragen sind die umgedrehten Regeln: Jede listet die Zeilen auf, die ihre Regel ablehnen würde.
  • Ausleihe 6 wird gelöscht, weil niemand sagen kann, wem sie gehörte; Ausleihe 7 behält beide Daten, jetzt in der richtigen Reihenfolge; Persuasion bekommt 1 Exemplar.
  • Die Primärschlüssel kommen vor den Fremdschlüsseln: Ein Fremdschlüssel braucht einen Primärschlüssel oder eine UNIQUE-Spalte, auf die er verweisen kann.
  • pg_constraint listet die Regeln von loans nach Typ auf: c ist der CHECK, f sind die zwei Fremdschlüssel, p der Primärschlüssel und n das NOT NULL, das der Primärschlüssel auf id gelegt hat.
Ä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

Schritt 1: die fehlerhaften Zeilen finden und korrigieren

Der Startcode findet die drei fehlerhaften Zeilen. Korrigieren Sie sie: Ausleihe 6 lässt sich keinem Mitglied zuordnen, also entfernen Sie sie; Ausleihe 7 ging am 2026-09-15 hinaus und kam am 2026-09-25 zurück, die Daten wurden vertauscht; Persuasion hat 1 Exemplar. Behalten Sie jede andere Zeile.

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

    DELETE FROM loans WHERE id = 6; entfernt genau die Ausleihe für Mitglied 9.

  2. Hinweis 2

    Setzen Sie für Ausleihe 7 beide Daten: UPDATE loans SET loaned_on = '2026-09-15', returned_on = '2026-09-25' WHERE id = 7;

  3. Hinweis 3

    Persuasion ist Buch 7: UPDATE books SET copies = 1 WHERE id = 7;

Eine Lösung zeigen

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

DELETE FROM loans WHERE id = 6;
UPDATE loans SET loaned_on = '2026-09-15', returned_on = '2026-09-25' WHERE id = 7;
UPDATE books SET copies = 1 WHERE id = 7;
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 whose member does not exist.
SELECT l.id, l.member_id
FROM loans l
LEFT JOIN members m ON m.id = l.member_id
WHERE m.id IS NULL;

-- Loans that came back before they went out.
SELECT id, loaned_on, returned_on FROM loans WHERE returned_on < loaned_on;

-- Books without copies.
SELECT id, title FROM books WHERE copies IS NULL;

test.sql

-- test: Jede Ausleihe nennt ein vorhandenes Mitglied
SELECT NOT EXISTS (SELECT 1 FROM loans l LEFT JOIN members m ON m.id = l.member_id WHERE m.id IS NULL);

-- test: Ausleihe 7 ging am 2026-09-15 hinaus und kam am 2026-09-25 zurück
SELECT loaned_on = '2026-09-15' AND returned_on = '2026-09-25' FROM loans WHERE id = 7;

-- test: Keine Ausleihe kommt zurück, bevor sie hinausging
SELECT NOT EXISTS (SELECT 1 FROM loans WHERE returned_on < loaned_on);

-- test: Persuasion hat 1 Exemplar
SELECT copies = 1 FROM books WHERE id = 7;

-- test: Sechs Ausleihen, sieben Bücher und vier Mitglieder bleiben
SELECT (SELECT count(*) FROM loans) = 6 AND (SELECT count(*) FROM books) = 7 AND (SELECT count(*) FROM members) = 4;

setup.sql

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);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

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');

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);

-- Rows that got in while nothing was checked.
INSERT INTO books VALUES (7, 'Persuasion', 'Jane Austen', 1817, 'classic', NULL);
INSERT INTO loans VALUES
  (6, 5, 9, '2026-09-20', NULL),
  (7, 4, 4, '2026-09-25', '2026-09-15');

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.

Übung 2 von 2

Schritt 2: die Regeln hinzufügen

Die Daten sind jetzt sauber. Fügen Sie die Regeln mit ALTER TABLE hinzu: einen Primärschlüssel auf id in books, members und loans; Fremdschlüssel von loans.book_id zu books und von loans.member_id zu members; CHECK (returned_on >= loaned_on) auf loans; copies NOT NULL mit Standardwert 1; und eine neue Spalte active in members, boolean, NOT NULL, true für alle.

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

    Zuerst die Primärschlüssel: ALTER TABLE books ADD PRIMARY KEY (id); und dasselbe für members und loans.

  2. Hinweis 2

    ALTER TABLE loans ADD FOREIGN KEY (member_id) REFERENCES members (id); und dasselbe für book_id.

  3. Hinweis 3

    ALTER COLUMN copies SET NOT NULL und SET DEFAULT 1 sind zwei Anweisungen; ADD COLUMN active boolean NOT NULL DEFAULT true füllt die vier Mitglieder.

Eine Lösung zeigen

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

ALTER TABLE books ADD PRIMARY KEY (id);
ALTER TABLE members ADD PRIMARY KEY (id);
ALTER TABLE loans ADD PRIMARY KEY (id);
ALTER TABLE loans ADD FOREIGN KEY (book_id) REFERENCES books (id);
ALTER TABLE loans ADD FOREIGN KEY (member_id) REFERENCES members (id);
ALTER TABLE loans ADD CHECK (returned_on >= loaned_on);
ALTER TABLE books ALTER COLUMN copies SET NOT NULL;
ALTER TABLE books ALTER COLUMN copies SET DEFAULT 1;
ALTER TABLE members ADD COLUMN active boolean NOT NULL DEFAULT true;
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

-- The data is clean. Add the rules here, keys first.
SELECT count(*) FROM loans;

test.sql

-- test: books, members und loans haben einen Primärschlüssel auf id
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') AND 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 = 'members'::regclass AND c.contype = 'p' AND cardinality(c.conkey) = 1 AND a.attname = 'id') AND 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: loans.book_id verweist auf books
SELECT EXISTS (SELECT 1 FROM pg_constraint WHERE conrelid = 'loans'::regclass AND contype = 'f' AND confrelid = 'books'::regclass);

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

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

-- test: copies ist NOT NULL mit Standardwert 1
SELECT a.attnotnull AND pg_get_expr(d.adbin, d.adrelid) = '1' FROM pg_attribute a JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum WHERE a.attrelid = 'books'::regclass AND a.attname = 'copies';

-- test: Jedes Mitglied ist active, und active ist NOT NULL
SELECT (SELECT count(*) FROM members WHERE active) = 4 AND (SELECT attnotnull FROM pg_attribute WHERE attrelid = 'members'::regclass AND attname = 'active');

setup.sql

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);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

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');

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);

-- Rows that got in while nothing was checked.
INSERT INTO books VALUES (7, 'Persuasion', 'Jane Austen', 1817, 'classic', NULL);
INSERT INTO loans VALUES
  (6, 5, 9, '2026-09-20', NULL),
  (7, 4, 4, '2026-09-25', '2026-09-15');

-- Step 1: the bad rows, fixed.
DELETE FROM loans WHERE id = 6;
UPDATE loans SET loaned_on = '2026-09-15', returned_on = '2026-09-25' WHERE id = 7;
UPDATE books SET copies = 1 WHERE id = 7;

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

Den Fremdschlüssel hinzufügen, bevor die Daten korrigiert sind

ALTER TABLE members ADD PRIMARY KEY (id);
ALTER TABLE loans ADD FOREIGN KEY (member_id) REFERENCES members (id);

Was psql ausgibt

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

Warum, und die Lösung

Der neue Fremdschlüssel wird an jeder Ausleihe geprüft, und Ausleihe 6 nennt Mitglied 9, das es nicht gibt; die DETAIL-Zeile nennt den Schlüssel. Finden Sie solche Zeilen mit LEFT JOIN members … WHERE m.id IS NULL, korrigieren oder löschen Sie sie und fügen Sie dann den Fremdschlüssel hinzu.

Ein Fremdschlüssel auf eine Tabelle ohne Schlüssel

ALTER TABLE loans ADD FOREIGN KEY (book_id) REFERENCES books (id);

Was psql ausgibt

ERROR:  there is no unique constraint matching given keys for referenced table "books"

Warum, und die Lösung

Ein Fremdschlüssel muss auf einen Primärschlüssel oder eine UNIQUE-Spalte verweisen, damit jeder Wert genau eine Zeile findet. Fügen Sie zuerst ALTER TABLE books ADD PRIMARY KEY (id); hinzu.

Ein CHECK, den die vorhandenen Zeilen verletzen

ALTER TABLE loans ADD CHECK (returned_on >= loaned_on);

Was psql ausgibt

ERROR:  check constraint "loans_check" of relation "loans" is violated by some row

Warum, und die Lösung

Ausleihe 7 kam zurück, bevor sie hinausging. Listen Sie solche Zeilen mit WHERE returned_on < loaned_on auf, korrigieren Sie die Daten mit UPDATE und fügen Sie den CHECK dann erneut hinzu.

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

Die fehlerhaften Zeilen finden, bevor Sie eine Regel hinzufügen

Eine mit ALTER TABLE hinzugefügte Regel wird sofort an jeder Zeile geprüft; fragen Sie also zuerst, welche Zeilen sie verletzen würden. Jede Regel hat ihre Abfrage. Ein Fremdschlüssel von loans zu members: die Ausleihen ohne Mitglied, LEFT JOIN members m … WHERE m.id IS NULL. Ein CHECK (returned_on >= loaned_on): WHERE returned_on < loaned_on; offene Ausleihen haben dort NULL und kommen durch, in der Abfrage wie im CHECK. Ein NOT NULL auf copies: WHERE copies IS NULL. In der Bibliothek finden diese Abfragen Ausleihe 6 für ein Mitglied 9, das es nicht gibt, Ausleihe 7, deren Daten verkehrt herum eingetippt wurden, und Persuasion, das keine Exemplare hat.

Erst die Daten korrigieren, dann die Regeln der Reihe nach

Entscheiden Sie für jede fehlerhafte Zeile, was stimmt. Ausleihe 6 lässt sich niemandem zuordnen, also DELETE. Ausleihe 7 behält beide Daten, in der richtigen Reihenfolge: UPDATE setzt sie neu (SET loaned_on = returned_on, returned_on = loaned_on geht auch, weil SET die alten Werte liest). Persuasion bekommt 1 Exemplar. Dann die Regeln: zuerst die Primärschlüssel, denn ein Fremdschlüssel darf nur auf einen Primärschlüssel oder eine UNIQUE-Spalte verweisen; members ohne einen ergibt there is no unique constraint matching given keys for referenced table "members". Dann die Fremdschlüssel, der CHECK, SET NOT NULL, SET DEFAULT 1 und die neue Spalte mit ihrem DEFAULT.

Beweisen, dass die Regeln gelten

Eine Regel, die Sie nie scheitern sahen, ist eine Regel, auf die Sie hoffen. Probieren Sie nach dem Projekt aus, was die Regeln ablehnen sollen, eine Anweisung nach der anderen: Eine Ausleihe für Mitglied 9 scheitert mit violates foreign key constraint "loans_member_id_fkey"; eine Ausleihe, die vor dem Verleih zurückkam, mit violates check constraint "loans_check"; ein Buch mit copies NULL mit violates not-null constraint; das Löschen von Ada, die Ausleihen hat, mit is still referenced from table "loans". Probieren Sie dann aus, was klappen soll: Ein Buch ohne copies bekommt jetzt den Standardwert 1. Zählen Sie auch die Zeilen, damit Sie wissen, dass das Projekt behalten hat, was es sollte: sechs Ausleihen, sieben Bücher, vier Mitglieder.

Quellen

Zuletzt geprüft am 4. Oktober 2026