LU08 – Praxisauftrag: Fremdschlüssel per ALTER TABLE + DML üben

Ziel der Übung: Ihr greift euren LU05-Case wieder auf (z. B. *Tierheim*, *Postzustellung*, *Online-Kleidershop*, *Eishockeyverein*, *ÖV*, *Schweizer Regionen*). Damals habt ihr Tabellen ohne Fremdschlüssel erstellt und Daten via WebStorm-Interface eingefügt. Jetzt

1. ergänzt ihr Fremdschlüssel mit ALTER TABLE, 2. testet die Referenzaktionen (Standard: RESTRICT), 3. übt DML (INSERT, UPDATE, DELETE) aus LU07.

Arbeitsform: 2er-Gruppen · Zeit: 1–2 Lektionen · Hilfsmittel: Kursunterlagen LU07/LU08 · Abgabe: siehe unten

—

* Öffnet eure LU05-Datenbank in WebStorm/MySQL. * Prüft, dass Primärschlüssel vorhanden sind (wie in LU05), aber keine Fremdschlüssel. * Falls ihr neu starten wollt: erstellt die Tabellen nochmals wie in LU05 (PK ja, FK noch nicht) und füllt ein paar Beispieldaten ein (mind. 3–5 Zeilen pro Tabelle mit Beziehungen).

—

Analysiert euer ERD (Crow’s Foot aus LU05). Legt für jede 1:N-Beziehung den Fremdschlüssel fest:

Beispiele (je nach Case anpassen):

* Tierheim:

''tier.art_id'' → ''tierart.art_id'' (N:1)
''tier.gehege_id'' → ''gehege.gehege_id'' (N:1)

* Postzustellung:

''briefkasten.gebiet_id'' → ''postgebiet.gebiet_id'' (N:1)
''poestler.gebiet_id'' → ''postgebiet.gebiet_id'' (N:1)

* Online-Kleidershop:

''kleidungsstueck.kategorie_id'' → ''kategorie.kategorie_id'' (N:1)
''kleidungsstueck.marke_id'' → ''marke.marke_id'' (N:1)

* Eishockeyverein:

''block.team_id'' → ''team.team_id'' (N:1)
''spieler.block_id'' → ''block.block_id'' (N:1)
''team.trainer_id'' → ''trainer.trainer_id'' (optional, wenn 1 Trainer mehrere Teams betreuen kann)

* ÖV (vereinfacht):

''haltestelle.linie_id'' → ''linie.linie_id'' (N:1)
''fahrt.linie_id'' → ''linie.linie_id'' (N:1)

* Schweizer Regionen:

''gemeinde.kanton_code'' → ''kanton.kanton_code'' (N:1)
''gemeinde.kva_id'' → ''kva.anlage_id'' (N:1)

SQL-Muster (anpassen):

ALTER TABLE <kind_tabelle>
ADD CONSTRAINT fk_<kind>_<eltern>
FOREIGN KEY (<fk_spalte>)
REFERENCES <eltern_tabelle>(<pk_spalte>)
ON DELETE RESTRICT
ON UPDATE RESTRICT; 

—

Bevor ihr FKs setzt, prüft mit einer Links-Prüfung, ob es verwaiste Daten gibt:

-- Beispiel: Kinder ohne Eltern finden
SELECT k.*
FROM <kind_tabelle> k
LEFT JOIN <eltern_tabelle> e
  ON k.<fk_spalte> = e.<pk_spalte>
WHERE e.<pk_spalte> IS NULL;

*Falls es Treffer gibt:* Korrigiert die Werte (UPDATE) oder löscht fehlerhafte Zeilen (DELETE), dann erst den FK setzen.

—

Führt die folgenden Mini-Tests in eurem Case durch. Erwartetes Verhalten jeweils in Klammern.

A. INSERT (gültige vs. ungültige FK-Werte)

-- gültig (Eltern existieren)
INSERT INTO <kind_tabelle>(..., <fk_spalte>) VALUES (..., <existierender_pk>);
 
-- ungültig (Eltern existieren nicht) → sollte scheitern
INSERT INTO <kind_tabelle>(..., <fk_spalte>) VALUES (..., 999999); 

B. DELETE Eltern (mit vorhandenen Kindern)

-- sollte scheitern (RESTRICT)
DELETE FROM <eltern_tabelle>
WHERE <pk_spalte> = <wert_mit_kindern>; 

C. UPDATE FK in Kind (umhängen auf anderen Eltern-Datensatz)

-- erlaubt, solange der neue Eltern-Datensatz existiert
UPDATE <kind_tabelle>
SET <fk_spalte> = <anderer_existierender_pk>
WHERE <kind_pk> = <id>; 

D. UPDATE PK in Eltern (Primärschlüsselwert ändern)

-- in der Praxis selten und oft verhindert (RESTRICT).
-- Erwartung: scheitert, wenn Kindzeilen darauf verweisen.
UPDATE <eltern_tabelle>
SET <pk_spalte> = <neuer_pk>
WHERE <pk_spalte> = <alter_pk>; 

—

Falls in eurem Modell eine Beziehung optional ist (FK darf NULL sein), könnt ihr ON DELETE SET NULL wählen:

ALTER TABLE <kind_tabelle>
  ADD CONSTRAINT fk_<kind>_<eltern> 
    FOREIGN KEY (<fk_spalte>)
    REFERENCES <eltern_tabelle>(<pk_spalte>)
    ON DELETE SET NULL
    ON UPDATE RESTRICT;

*Einsatzbeispiel:* team.trainer_id darf leer sein → beim Löschen eines Trainers bleibt das Team bestehen, trainer_id wird NULL.

CASCADE verwenden wir bevorzugt bei Zwischentabellen (N:M). Falls euer Case eine N:M-Erweiterung hat (z. B. im Kleidershop: *Artikel ↔ Größe*), legt ihr die Junction-Tabelle in LU08e an und setzt dort CASCADE. Für reine 1:N-Beziehungen bleibt RESTRICT meist die beste Wahl.

—

1. Kurz-Doku (max. 1 Seite) mit:

• ERD (Crow’s Foot) mit PK/FK markiert.
• Liste eurer ALTER TABLE-Befehle (FK-Namen, ON DELETE/UPDATE-Regeln).
• 3–5 DML-Beispiele (INSERT/UPDATE/DELETE) inkl. kurzer Interpretation des Verhaltens (z. B. Fehlermeldung bei RESTRICT).

2. SQL-Datei mit allen ausgeführten ALTER/INSERT/UPDATE/DELETE (in sinnvoller Reihenfolge). 3. Kurz-Demo (2–3 Minuten): Ein FK-Fehler zeigen und erklären, warum er sinnvoll ist.

—

*Tierheim*

ALTER TABLE tier
ADD CONSTRAINT fk_tier_art
FOREIGN KEY (art_id) REFERENCES tierart(art_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT fk_tier_gehege
FOREIGN KEY (gehege_id) REFERENCES gehege(gehege_id) ON DELETE RESTRICT ON UPDATE RESTRICT; 

*Postzustellung*

ALTER TABLE briefkasten
ADD CONSTRAINT fk_briefkasten_gebiet
FOREIGN KEY (gebiet_id) REFERENCES postgebiet(gebiet_id) ON DELETE RESTRICT ON UPDATE RESTRICT;
 
ALTER TABLE poestler
ADD CONSTRAINT fk_poestler_gebiet
FOREIGN KEY (gebiet_id) REFERENCES postgebiet(gebiet_id) ON DELETE RESTRICT ON UPDATE RESTRICT; 

*Online-Kleidershop*

ALTER TABLE kleidungsstueck
ADD CONSTRAINT fk_kleid_kategorie
FOREIGN KEY (kategorie_id) REFERENCES kategorie(kategorie_id) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT fk_kleid_marke
FOREIGN KEY (marke_id) REFERENCES marke(marke_id) ON DELETE RESTRICT ON UPDATE RESTRICT; 

*Eishockeyverein*

ALTER TABLE block
ADD CONSTRAINT fk_block_team
FOREIGN KEY (team_id) REFERENCES team(team_id) ON DELETE RESTRICT ON UPDATE RESTRICT;
 
ALTER TABLE spieler
ADD CONSTRAINT fk_spieler_block
FOREIGN KEY (block_id) REFERENCES block(block_id) ON DELETE RESTRICT ON UPDATE RESTRICT;
 
ALTER TABLE team
ADD CONSTRAINT fk_team_trainer
FOREIGN KEY (trainer_id) REFERENCES trainer(trainer_id) ON DELETE SET NULL ON UPDATE RESTRICT; -- optional 

*Öffentlicher Verkehr (vereinfacht)*

ALTER TABLE haltestelle
ADD CONSTRAINT fk_haltestelle_linie
FOREIGN KEY (linie_id) REFERENCES linie(linie_id) ON DELETE RESTRICT ON UPDATE RESTRICT;
 
ALTER TABLE fahrt
ADD CONSTRAINT fk_fahrt_linie
FOREIGN KEY (linie_id) REFERENCES linie(linie_id) ON DELETE RESTRICT ON UPDATE RESTRICT; 

*Schweizer Regionen*

ALTER TABLE gemeinde
ADD CONSTRAINT fk_gemeinde_kanton
FOREIGN KEY (kanton_code) REFERENCES kanton(kanton_code) ON DELETE RESTRICT ON UPDATE RESTRICT,
ADD CONSTRAINT fk_gemeinde_kva
FOREIGN KEY (kva_id) REFERENCES kva(anlage_id) ON DELETE RESTRICT ON UPDATE RESTRICT;