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 Zeilen pro Tabelle).

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;

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 Zwischen-Tabelle wie in LU08e an und setzt dort CASCADE. Für reine 1:N-Beziehungen bleibt RESTRICT meist die beste Wahl.

Kurz-Beschrieb (Word-Dokument/PDF, 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).