modul:m290_guko:learningunits:lu08:theorie:c_fk-create-table

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
Nächste Überarbeitung		 Vorhergehende Überarbeitung
modul:m290_guko:learningunits:lu08:theorie:c_fk-create-table [2025/10/19 21:51] – gkochmodul:m290_guko:learningunits:lu08:theorie:c_fk-create-table [2025/11/05 11:23] (aktuell) – gkoch
Zeile 5: Zeile 5:
 ==== ERD (Überblick) ==== ==== ERD (Überblick) ====
 Wir gehen vom Schema aus dem Reiseblog-Beispiel aus: 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> <WRAP tip round 80% center>
 Posts können mehreren Kategorien angehören (N:M). Die saubere Lösung ist eine Zwischentabelle ''post_category''.  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> </WRAP>
  
  
 ===== Fremdschlüssel: Grundsyntax ===== ===== 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%> <WRAP box round center 80%>
 <code sql> <code sql>
Zeile 26: Zeile 31:
  
 ===== Beispiel Reiseblog ===== ===== 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 ==== ==== 1. Tabellen anlegen ====
Zeile 135: Zeile 144:
 === Verknüpfung Tabelle users & posts (one-to-many) === === 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) === === 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) ==== ==== 3. Fremdschlüssel in Aktion (Standard: RESTRICT) ====
-Beim setzen von Fremdschlüsseln macht MySQL etwas im Hintergrund: es sichert die zwei verbundenen Tabellen bzw. die Datensätzen deren gegen änderungen und Löschungen ab. Das nennt man Referenzaktion und standartmässig setzt hier MySQL //Restrict// ein.  +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.
-Was heisst das für unser Reiseblog-Beispiel. Wir haben zwei Tabellen //users// und //posts//. Der Fremdschlüssel ''author_id'' wurde bei //posts// gesetzt und verweist auf die //user_id// in //users//. Das heisst die Eltern-Tabelle ist hier //users// während die Kind-Tabelle //posts// nun abhängig ist von dessen Eltern. Die Elterntabelle schützt nun MySQL gegen gewisse Änderungen und Löschungen: Löschen/Ändern der Elternzeile ist nur erlaubt, wenn keine Kindzeilen darauf verweisen.+
  
-<WRAP center box 80% round><code sql> +Bezogen auf unser Reiseblog-Beispiel: ''posts.author_id'' verweist auf ''users.user_id''. 
--- Sicherheit: Welche FKs sind gesetzt? +Damit ist //users// die Elterntabelle und //posts// die Kindtabelle. Die Folge von ''RESTRICT'': 
-SHOW CREATE TABLE posts; + 
-</code></WRAP>+  * Löschen eines Users ist blockiert, solange Posts auf diesen User verweisen. 
 +  * Ä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. 
 + 
 +Probieren Sie folgende Codesnippets in Webstorm/MySQL, damit Sie gleich das entsprechende Gefühl dafür bekommen, was //RESTRICT// passiert.
  
-Demo 1 – User ohne Posts löschen (erlaubt):+=== Demo 1 – User ohne Posts löschen (erlaubt) ===
 <WRAP center box 80% round><code sql> <WRAP center box 80% round><code sql>
 DELETE FROM users DELETE FROM users
Zeile 156: Zeile 168:
 SELECT user_id, username FROM users; SELECT user_id, username FROM users;
 </code></WRAP> </code></WRAP>
-Erwartung: Die Zeile wird gelöscht (keine Posts verweisen auf den User).+//Erwartung: Die Zeile wird gelöscht (keine Posts verweisen auf den User).//
  
-Demo 2 – User mit Posts löschen (blockiert):+=== Demo 2 – User mit Posts löschen (blockiert) ===
 <WRAP center box 80% round><code sql> <WRAP center box 80% round><code sql>
 DELETE FROM users DELETE FROM users
 WHERE username = 'martin'; WHERE username = 'martin';
 </code></WRAP> </code></WRAP>
-Erwartete Fehlermeldung (sinngemäss): +//Erwartete Fehlermeldung (sinngemäss):// 
-<WRAP alert round 80%>+<WRAP alert round 80% center>
 [23000][1451] Cannot delete or update a parent row: a foreign key constraint fails [23000][1451] Cannot delete or update a parent row: a foreign key constraint fails
 (travel_blog.posts, CONSTRAINT posts_ibfk_1 FOREIGN KEY (author_id) REFERENCES users(user_id)) (travel_blog.posts, CONSTRAINT posts_ibfk_1 FOREIGN KEY (author_id) REFERENCES users(user_id))
 </WRAP> </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.+//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):+=== Demo 3 – Unkritisches Attribut ändern (erlaubt) ===
 <WRAP center box 80% round><code sql> <WRAP center box 80% round><code sql>
 UPDATE users UPDATE users
Zeile 177: Zeile 189:
 </code></WRAP> </code></WRAP>
  
-Demo 4 – Primärschlüssel ändern (blockiert, RESTRICT):+=== Demo 4 – Primärschlüssel ändern (blockiert) ===
 <WRAP center box 80% round><code sql> <WRAP center box 80% round><code sql>
 UPDATE users UPDATE users
Zeile 183: Zeile 195:
 WHERE user_id = 2; -- erwartet: Fehler (RESTRICT), da posts.author_id -> users.user_id WHERE user_id = 2; -- erwartet: Fehler (RESTRICT), da posts.author_id -> users.user_id
 </code></WRAP> </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.+//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 – Kategorie-ID ändern: Was passiert?+=== Demo 5 – Primärschlüssel ändern: Geht das? (ja) ===
 <WRAP center box 80% round><code sql> <WRAP center box 80% round><code sql>
 UPDATE categories UPDATE categories
Zeile 201: Zeile 213:
  
 <WRAP tip round 80% center> <WRAP tip round 80% center>
-Merke: Fremdschlüssel geben dir Daten­sicherheit: +Merke: Fremdschlüssel geben Daten­sicherheit:
   * Verhindern verwaiste Daten (z. B. Posts ohne gültigen Autor),   * Verhindern verwaiste Daten (z. B. Posts ohne gültigen Autor),
   * definieren klares Verhalten bei Löschen/Ändern (RESTRICT, CASCADE, SET NULL),   * definieren klares Verhalten bei Löschen/Ändern (RESTRICT, CASCADE, SET NULL),
  • modul/m290_guko/learningunits/lu08/theorie/c_fk-create-table.1760903468.txt.gz
  • Zuletzt geändert: 2025/10/19 21:51
  • von gkoch