Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- modul:m290_guko:learningunits:lu08:theorie:c_fk-create-table [2025/10/19 21:03] – gkoch
+++ modul:m290_guko:learningunits:lu08:theorie:c_fk-create-table [2025/11/05 11:23] (aktuell) – gkoch
@@ Zeile 5: / Zeile 5: @@
 ==== ERD (Überblick) ====
 Wir gehen vom Schema aus dem Reiseblog-Beispiel aus:
-{{ :modul:m290_guko:learningunits:lu08:theorie:reiseblog_crows-foot.drawio.png?nolink&1100 | ERD: Blog Post – User (1:N) und Blog Post – Category (N:M) }}
+{{ :modul:m290_guko:learningunits:lu08:theorie:crowsfoot_wordpress_reiseblog.drawio.png?nolink&1100 | ERD: Blog Post – User (1:N) und Blog Post – Category (N:M) }}
 <WRAP tip round 80% center>
 Posts können mehreren Kategorien angehören (N:M). Die saubere Lösung ist eine Zwischentabelle ''post_category''.
-Das bauen wir später (s. LU08e: N:M-Beziehungen mit Zwischentabelle abbilden.
+Das bauen wir später (s. LU08e: N:M-Beziehungen mit Zwischentabelle abbilden).
 </WRAP>
 ===== Fremdschlüssel: Grundsyntax =====
+<WRAP center tip round 80%>
+[[https://www.youtube.com/watch?v=QDmJBSgnUEo|SQL Schlüssel einfach erklärt – SQL 3]]((Informatik – simpleclub / YouTube)) -> (7:49, de) Primär- und Fremdschlüssel anschaulich erklärt.
+</WRAP>
 <WRAP box round center 80%>
 <code sql>
@@ Zeile 26: / Zeile 31: @@
 ===== Beispiel Reiseblog =====
+<WRAP center tip round 80%>
+[[https://www.youtube.com/watch?v=i-W5bos0LlE|SQL Tabellenerzeugung mit DDL – SQL 2]]((Informatik – simpleclub / YouTube)) -> (7:46, de) Kurz und klar: CREATE TABLE, Datentypen, Constraints – ideal als DDL-Auffrischung vor dem Setzen von FKs.
+</WRAP>
 ==== 1. Tabellen anlegen ====
@@ Zeile 135: / Zeile 144: @@
 === Verknüpfung Tabelle users & posts (one-to-many) ===
-{{ :modul:m290_guko:learningunits:lu08:theorie:reiseblog-connected_users-posts.png?direct&1600 |}}
+{{:modul:m290_guko:learningunits:lu08:theorie:reiseblog-connected_users-posts.png?direct&1600|}}
 === Tabelle categories (wird später mit 'posts' verknüpft) ===
-{{ :modul:m290_guko:learningunits:lu08:theorie:categories.png?direct&400 |}}
+{{:modul:m290_guko:learningunits:lu08:theorie:categories.png?direct&400|}}
+==== 3. Fremdschlüssel in Aktion (Standard: RESTRICT) ====
+Beim Setzen von Fremdschlüsseln überwacht MySQL/MariaDB Änderungen an den verknüpften Spalten und verhindert Operationen, die zu inkonsistenten Verweisen führen. Diese Reaktion heisst //Referenzaktion//. Standardmässig gilt ''RESTRICT'': Eine Zeile in der Elterntabelle darf nicht gelöscht werden und ihr Primärschlüssel darf nicht geändert werden, solange Kindzeilen auf sie zeigen.
+Bezogen auf unser Reiseblog-Beispiel: ''posts.author_id'' verweist auf ''users.user_id''.
+Damit ist //users// die Elterntabelle und //posts// die Kindtabelle. Die Folge von ''RESTRICT'':
-==== 3. Mehrere Tabellen abfragen ====
+  * Löschen eines Users ist blockiert, solange Posts auf diesen User verweisen.
-Wenn wir nur die Tabelle //posts// abfragen, sehen wir in der Spalte //author_id// «nur» die ID (Zahl) aus //users.user_id//. Möchten wir wissen, **wer** den Beitrag verfasst hat, müssen wir **posts** und **users** gemeinsam abfragen.
+  * Ändern von ''users.user_id'' ist blockiert, solange es verweisende ''posts.author_id'' gibt.
+  * Änderungen an nicht referenzierten Spalten (z. B. ''users.username'') sind weiterhin erlaubt.
-Das geht mit der bekannten SELECT-Schreibweise: Wir nennen nach //FROM// **beide Tabellen** (mit **Kommas** getrennt) und setzen in der **WHERE-Klausel** den Fremdschlüssel //posts.author_id// gleich dem Primärschlüssel //users.user_id//.
+Probieren Sie folgende Codesnippets in Webstorm/MySQL, damit Sie gleich das entsprechende Gefühl dafür bekommen, was //RESTRICT// passiert.
-Durch die Angabe von //users.display_name AS author// erhalten wir zusätzlich die Spalte mit dem gewünschten Anzeigenamen.
+=== Demo 1 – User ohne Posts löschen (erlaubt) ===
 <WRAP center box 80% round><code sql>
-SELECT posts.post_id, posts.title, users.display_name AS author
+DELETE FROM users
-FROM posts, users
+WHERE username = 'shaolin'; -- hat keine Posts zugewiesen
-WHERE posts.author_id = users.user_id
+SELECT user_id, username FROM users;
-ORDER BY posts.post_id;
 </code></WRAP>
+//Erwartung: Die Zeile wird gelöscht (keine Posts verweisen auf den User).//
-**Mögliches Resultat:**
+=== Demo 2 – User mit Posts löschen (blockiert) ===
+<WRAP center box 80% round><code sql>
-<WRAP center box 80% round>
+DELETE FROM users
-^ post_id ^ title ^ author ^
+WHERE username = 'martin';
-| 1 | Hasselt – 10 Highlights | Martin Merten |
+</code></WRAP>
-| 2 | Utrecht – 10 Sehenswürdigkeiten | Martin Merten |
+//Erwartete Fehlermeldung (sinngemäss)://
-| 3 | Lissabon – 8 Tipps fürs erste Mal | Caro Steig |
+<WRAP alert round 80% center>
-| 4 | Maastricht an einem Tag | Caro Steig |
+[23000][1451] Cannot delete or update a parent row: a foreign key constraint fails
-| 5 | Montenegro Roadtrip – 10 Highlights | Caro Steig |
+(travel_blog.posts, CONSTRAINT posts_ibfk_1 FOREIGN KEY (author_id) REFERENCES users(user_id))
-| 6 | Oman – Top 22 Highlights | Caro Steig |
-| 7 | Chicago in 3 Tagen – 17 Highlights | Martin Merten |
 </WRAP>
+//Grund: In posts.author_id gibt es Kindzeilen (z.B. "Hasselt – 10 Highlights"), die auf users.user_id von martin verweisen → RESTRICT verhindert das Löschen.//
+=== Demo 3 – Unkritisches Attribut ändern (erlaubt) ===
+<WRAP center box 80% round><code sql>
+UPDATE users
+SET username = 'martin_travels'
+WHERE user_id = 2; -- OK: FKs verweisen auf user_id, nicht auf username
+</code></WRAP>
+=== Demo 4 – Primärschlüssel ändern (blockiert) ===
+<WRAP center box 80% round><code sql>
+UPDATE users
+SET user_id = 5
+WHERE user_id = 2; -- erwartet: Fehler (RESTRICT), da posts.author_id -> users.user_id
+</code></WRAP>
+//Erklärung: posts.author_id → users.user_id ist ein FK mit Standard ON UPDATE RESTRICT. Solange Posts existieren, darf der user_id-Wert nicht verändert werden.//
+=== Demo 5 – Primärschlüssel ändern: Geht das? (ja) ===
+<WRAP center box 80% round><code sql>
+UPDATE categories
+SET category_id = 11
+WHERE category_id = 10; -- USA → 11: funktioniert - Primary Keys dürfen geändert werden.
+</code></WRAP>
+=== Warum ist das so? ===
+//RESTRICT// (Standard) schützt die Elternzeile (z. B. users): Solange Kindzeilen (z. B. posts) auf sie zeigen, sind DELETE/UPDATE am referenzierten Primärschlüssel blockiert.
+Änderungen an nicht referenzierten Spalten (z. B. ''users.username'') sind frei möglich – FKs verweisen hier nicht darauf.
+Ob eine Änderung blockiert oder mitgezogen (CASCADE) wird, hängt von der ON DELETE/ON UPDATE-Einstellung im FK ab.
 <WRAP tip round 80% center>
-**Ausblick:** In LU09 formulieren wir Abfragen über mehrere Tabellen (u. a. Categories via //post_category//) strukturiert mit **JOINs**.
+Merke: Fremdschlüssel geben Datensicherheit:
+  * Verhindern verwaiste Daten (z. B. Posts ohne gültigen Autor),
+  * definieren klares Verhalten bei Löschen/Ändern (RESTRICT, CASCADE, SET NULL),
+  * halten die Datenbank konsistent.
 </WRAP>
+<wrap lo> Ausblick: In **LU08d** fügen wir FKs per **ALTER TABLE** nachträglich hinzu und testen die Referenzaktionen **RESTRICT**, **CASCADE** und **SET NULL** gezielt. </wrap>