Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- modul:m290_guko:learningunits:lu08:theorie:e_fk-abfragen [2025/10/11 08:52] – angelegt gkoch
+++ modul:m290_guko:learningunits:lu08:theorie:e_fk-abfragen [2025/10/11 09:32] (aktuell) – gkoch
@@ Zeile 1: / Zeile 1: @@
-====== LU08e – Referenzaktionen testen (RESTRICT, CASCADE, SET NULL) ======
+====== LU08e – Referenzaktionen testen ======
-===== 4.1) Testdaten anlegen =====
+Im Fachmodell sind beide FKs **RESTRICT**. Für die Didaktik zeigen wir zusätzlich *Demo-Varianten*, um
-<code sql>
+**CASCADE** und **SET NULL** sichtbar zu machen (auch wenn das fachlich hier weniger Sinn ergibt).
--- Personen
-INSERT INTO person(name)
-VALUES ('Sofia Coppola'), ('Scarlett Johansson'), ('Bill Murray');
--- Film
+===== 1) RESTRICT beobachten =====
-INSERT INTO film(title, released_year, director_id)
+Versuche, ein Land zu löschen, zu dem es Trips gibt (z. B. Italien):
-VALUES ('Lost in Translation', 2003,
+<WRAP center box 80% round><code sql>
-        (SELECT person_id FROM person WHERE name='Sofia Coppola'));
+DELETE FROM Country WHERE CountryName='Italien';   -- sollte mit RESTRICT fehlschlagen
+</code></WRAP>
--- Besetzung
+Versuche, den Kontinent „Europa“ zu löschen:
-INSERT INTO film_cast(film_id, person_id, role)
+<WRAP center box 80% round><code sql>
-SELECT f.film_id, p.person_id, 'Lead'
+DELETE FROM Continent WHERE ContinentName='Europa'; -- sollte mit RESTRICT fehlschlagen
-FROM film f JOIN person p ON p.name IN ('Scarlett Johansson','Bill Murray')
+</code></WRAP>
-WHERE f.title='Lost in Translation';
-</code>
-===== 4.2) RESTRICT beobachten =====
+===== 2) Demo: CASCADE (nur zum Beobachten) =====
-Person in Cast kann nicht gelöscht werden:
+**Hinweis:** Nicht dauerhaft im Produktivschema verwenden — hier nur zum Verstehen der Wirkung.
-<code sql>
-DELETE FROM person WHERE name='Scarlett Johansson';  -- sollte FEHLER liefern (RESTRICT)
-</code>
-===== 4.3) SET NULL beobachten =====
+) FK von Trip→Country temporär auf **CASCADE** ändern:
-Regie löschen → Film bleibt, Regie-FK wird NULL:
+<WRAP center box 80% round><code sql>
-<code sql>
+ALTER TABLE Trip DROP FOREIGN KEY fk_trip_country;
-DELETE FROM person WHERE name='Sofia Coppola';
-SELECT film_id, title, director_id FROM film;
-</code>
-===== 4.4) CASCADE beobachten =====
+ALTER TABLE Trip
-Film löschen → zugehörige Cast-Zeilen verschwinden:
+  ADD CONSTRAINT fk_trip_country
-<code sql>
+  FOREIGN KEY (CountryID)
-DELETE FROM film WHERE title='Lost in Translation';
+  REFERENCES Country(CountryID)
-SELECT * FROM film_cast;  -- Cast-Einträge sollten weg sein
+  ON DELETE CASCADE
-</code>
+  ON UPDATE RESTRICT;
+</code></WRAP>
-===== 4.5) Typische Fehlerbilder =====
+) Lösche nun das Land „Deutschland“:
-  * FK-Spalte ist **NOT NULL**, Aktion = **SET NULL** ⇒ **Fehler** (unvereinbar).
+<WRAP center box 80% round><code sql>
-  * FK/PK-Datentypen passen nicht (z. B. SIGNED vs. UNSIGNED) ⇒ **Fehler**.
+DELETE FROM Country WHERE CountryName='Deutschland';
-  * ON-Bedingungen „passen nicht zur Geschäftsregel“ ⇒ unerwartete Lösch-/Update-Effekte.
+SELECT * FROM Trip;  -- zu DE gehörende Trips sind automatisch gelöscht
+</code></WRAP>
+) **Zurück** auf RESTRICT stellen (empfohlen):
+<WRAP center box 80% round><code sql>
+ALTER TABLE Trip DROP FOREIGN KEY fk_trip_country;
+ALTER TABLE Trip
+  ADD CONSTRAINT fk_trip_country
+  FOREIGN KEY (CountryID)
+  REFERENCES Country(CountryID)
+  ON DELETE RESTRICT
+  ON UPDATE RESTRICT;
+</code></WRAP>
+===== 3) Demo: SET NULL (nur zum Beobachten) =====
+**Hinweis:** Fachlich widerspricht SET NULL hier der Regel „Trip gehört zu genau einem Land“.
+Wir zeigen es rein technisch. Dafür muss `Trip.CountryID` NULL erlauben:
+<WRAP center box 80% round><code sql>
+-- 1) Spalte nullbar machen
+ALTER TABLE Trip MODIFY CountryID INT NULL;
+-- 2) FK auf SET NULL
+ALTER TABLE Trip DROP FOREIGN KEY fk_trip_country;
+ALTER TABLE Trip
+  ADD CONSTRAINT fk_trip_country
+  FOREIGN KEY (CountryID)
+  REFERENCES Country(CountryID)
+  ON DELETE SET NULL
+  ON UPDATE RESTRICT;
+-- 3) Land 'Schweiz' löschen -> CountryID in betroffenen Trips wird NULL
+DELETE FROM Country WHERE CountryName='Schweiz';
+SELECT TripID, CountryID, StartDate, EndDate, Price FROM Trip;
+</code></WRAP>
+**Merke:** **SET NULL** setzt voraus, dass die FK-Spalte `NULL` erlaubt – sonst Fehler.
+===== 4) Aufräumen (optional) =====
+<WRAP center box 80% round><code sql>
+-- CountryID wieder NOT NULL + FK zurück auf RESTRICT
+UPDATE Trip SET CountryID = (SELECT CountryID FROM Country WHERE CountryName='Italien')
+WHERE CountryID IS NULL;
+ALTER TABLE Trip MODIFY CountryID INT NOT NULL;
+ALTER TABLE Trip DROP FOREIGN KEY fk_trip_country;
+ALTER TABLE Trip
+  ADD CONSTRAINT fk_trip_country
+  FOREIGN KEY (CountryID)
+  REFERENCES Country(CountryID)
+  ON DELETE RESTRICT
+  ON UPDATE RESTRICT;
+</code></WRAP>