Aufgabe 11 - Schemaänderungen mit ALTER TABLE und DROP TABLE

Aufgabe 11 - Schemaänderungen mit ALTER TABLE und DROP TABLE (PostgreSQL)

Worum geht es?

In dieser Übung wird ein bestehendes Datenbankschema gezielt mit ALTER TABLE und DROP TABLE verändert (siehe Kapitel 5 - SQL-Grundlagen). Spalten und Constraints werden hinzugefügt, geändert und entfernt, immer unter Beachtung der Abhängigkeiten. Im Expertenteil entsteht eine sichere, mehrfach ausführbare Migration.

Was Sie dafür brauchen

• Kapitel 5 - SQL-Grundlagen, Abschnitt DDL.
• Ein PostgreSQL-Server und ein SQL-Editor (psql, pgAdmin, DBeaver).
• Eine Datei aufgabe11.sql, in der alle Befehle in Reihenfolge gesammelt werden.

Welche Kompetenzen Sie erwerben und zeigen

• Sie ändern Spalten und Datentypen mit ALTER TABLE.
• Sie bauen Constraints (CHECK, FOREIGN KEY, UNIQUE) durch Drop und Add um.
• Sie beachten Abhängigkeiten beim DROP TABLE und beim CASCADE.
• Sie schreiben eine sichere, idempotente Migration ohne Datenverlust.

Pädagogische Einordnung

• Reproduktion: das Ausgangsschema anlegen und testen (Teil A).
• Reorganisation und Transfer: Spalten und Constraints ändern (Teile B und C).
• Reflexion, Problemlösung und Urteilsbildung: eine sichere, wiederholbare Migration entwickeln (Teil D).

Arbeitsaufträge

Die Übung ist auf etwa zwei Stunden ausgelegt. Teil D ist der Expertenteil.

Ausgangsschema (Kursverwaltung)

CREATE TABLE course (
  course_id        SERIAL PRIMARY KEY,
  title            VARCHAR(120) NOT NULL,
  start_date       DATE NOT NULL,
  max_participants INTEGER NOT NULL CHECK (max_participants > 0)
);

CREATE TABLE participant (
  participant_id SERIAL PRIMARY KEY,
  student_number VARCHAR(20) NOT NULL UNIQUE,
  first_name     VARCHAR(60) NOT NULL,
  last_name      VARCHAR(60) NOT NULL,
  birth_date     DATE
);

CREATE TABLE registration (
  registration_id   SERIAL PRIMARY KEY,
  course_id         INTEGER NOT NULL REFERENCES course(course_id),
  participant_id    INTEGER NOT NULL REFERENCES participant(participant_id),
  registration_date DATE NOT NULL DEFAULT CURRENT_DATE,
  status            VARCHAR(15) NOT NULL CHECK (status IN ('registered', 'waitlisted', 'cancelled')),
  CONSTRAINT uq_registration UNIQUE (course_id, participant_id)
);

Teil A - Schema anlegen und testen

1. Die drei Tabellen exakt anlegen und mit \d course usw. kontrollieren.
2. Je Tabelle einige Testdaten einfügen (2 Kurse, 3 Teilnehmende, 4 Anmeldungen).
3. Bewusst mindestens einen fehlerhaften Insert testen (negativer max_participants, ungültiger status, doppelte Anmeldung) und die Fehlermeldung notieren.

Ab hier erfolgen alle Änderungen ausschließlich über ALTER TABLE und DROP TABLE.

Teil B - Spaltenänderungen

1. In participant eine Spalte email VARCHAR(120) ergänzen (zunächst NULL-fähig) und anschließend UNIQUE setzen.
2. Für alle bestehenden Teilnehmenden per UPDATE eine email eintragen, dann email auf NOT NULL setzen.
3. Die Spalte student_number in student_no umbenennen.
4. In course den Typ von title auf VARCHAR(160) ändern.
5. In participant die Spalte birth_date entfernen.

Teil C - Constraints und DROP TABLE

1. Den CHECK auf registration.status um den Wert completed erweitern: bestehenden Constraint identifizieren, DROP CONSTRAINT, neuen CHECK hinzufügen.
2. Die Fremdschlüssel in registration auf ON DELETE CASCADE umstellen (Constraint droppen und neu anlegen).
3. Den Constraint uq_registration entfernen und mit einem Insert zeigen, dass nun doppelte Anmeldungen möglich wären.
4. Die drei Tabellen in korrekter Reihenfolge löschen und begründen, warum die Reihenfolge zählt. Danach das Schema neu anlegen und nur course mit DROP TABLE ... CASCADE löschen; dokumentieren, welche abhängigen Objekte mitentfernt werden.

Teil D - Expertenteil: Eine sichere, idempotente Migration

Das Schema soll um eine E-Mail-Pflicht und eine schärfere Statusregel erweitert werden, und zwar so, dass die Migration mehrfach ausführbar ist, ohne beim zweiten Lauf Fehler zu werfen, und ohne bestehende Daten zu zerstören.

1. Datensichere Spalteneinführung: email in drei Schritten einführen (nullable hinzufügen → per UPDATE befüllen → NOT NULL setzen). Erklären, warum das direkte Hinzufügen einer NOT NULL-Spalte ohne Default an bestehenden Zeilen scheitern würde.

2. Partielle Eindeutigkeit: Eine Regel erzwingen, dass ein Teilnehmer pro Kurs höchstens eine nicht stornierte Anmeldung hat, mit einem partiellen Unique-Index:

   CREATE UNIQUE INDEX IF NOT EXISTS uq_active_registration
     ON registration (course_id, participant_id)
     WHERE status <> 'cancelled';

3. Idempotenz: Die gesamte Migration mit IF EXISTS / IF NOT EXISTS bzw. Prüfabfragen so schreiben, dass ein zweiter Lauf fehlerfrei durchläuft (z. B. ALTER TABLE ... ADD COLUMN IF NOT EXISTS, DROP CONSTRAINT IF EXISTS, CREATE INDEX IF NOT EXISTS). Die Migration zweimal hintereinander ausführen und zeigen, dass der zweite Lauf ohne Fehler bleibt.

4. Begründen: In vier bis fünf Sätzen erklären, warum Idempotenz und Datensicherheit bei Migrationen im Team und in der Produktion wichtig sind und warum manche Constraints in PostgreSQL nur über Drop und Add geändert werden können.

Wissenscheck

1. Warum muss ein CHECK- oder FOREIGN KEY-Constraint meist gelöscht und neu angelegt werden?
2. Was bewirkt ON DELETE CASCADE?
3. Warum ist die Reihenfolge beim DROP TABLE wichtig, und wie umgeht CASCADE das Problem?
4. Warum scheitert das Hinzufügen einer NOT NULL-Spalte ohne Default an einer befüllten Tabelle?
5. Was macht eine Migration idempotent, und warum ist das wertvoll?

Abgabe

• Die Datei aufgabe11.sql mit allen Befehlen in Reihenfolge, kommentiert je Schritt, inklusive der idempotenten Migration aus Teil D (zweimal lauffähig).
• Ein kurzes Protokoll (max. 1 Seite): welche Änderungen waren fehleranfällig, welche Constraints mussten gedroppt und neu angelegt werden, und was der zweite Migrationslauf gezeigt hat.

---

HTL Villach, Schuljahr 2025-2026,
https://www.htl-villach.at