de: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
de:modul:m290_guko:learningunits:lu08:theorie:c_fk-create-table [2026/10/17 13:17] – gkochde:modul:m290_guko:learningunits:lu08:theorie:c_fk-create-table [2026/10/17 16:40] (aktuell) – gkoch
Zeile 1: Zeile 1:
-====== LU08c: Tabellen mit Fremdschlüssel erstellen ======+====== LU08c - Fremdschlüssel nachträglich hinzufügen und Referenzaktionen ======
  
-**Ziel:** Wir verteilen die Daten wie bei WordPress auf mehrere Tabellen (**users**, **posts**, **categories** und die N:M-Zuordnung **post_category**) **und setzen die Fremdschlüssel direkt beim Erstellen**.+===== Lernziele ===== 
 +  * Ich kann mit ALTER TABLE eine Spalte und einen Fremdschlüssel zu einer bestehenden Tabelle hinzufügen. 
 +  * Ich kann erklären, was RESTRICT, CASCADE und SET NULL beim Löschen bewirken. 
 +  * Ich kann für eine Beziehung begründen, welche Referenzaktion passt.
  
-==== ERD (Überblick) ==== 
-Wir gehen vom Schema aus dem Reiseblog-Beispiel aus: 
  
-{{ de: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) }}+===== Wozu nachträglich? ===== 
 +Eine Datenbank entsteht selten in einem Zug. Oft gibt es die Tabellen schon, samt Daten, und dann kommt eine neue Spalte oder eine neue Verknüpfung dazu.
  
-<WRAP tip round 80% center> +Bei Ihren Cases aus LU05 ist das genau so. Dort haben Sie die Spalten für die Fremdschlüssel angelegt, aber noch nicht als Fremdschlüssel gesetzt. Das holen Sie mit **ALTER TABLE** nach.
-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). +
-</WRAP>+
  
 +Wir bleiben beim Reiseblog der letzten Seite. Zur Erinnerung: Caro hat die //user_id// 1, Martin die 2, Shaolin die 3.
  
-===== Fremdschlüssel: Grundsyntax ===== 
-<WRAP center tip round 80%> 
-{{youtube>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%> +===== ALTER TABLE ===== 
-<code sql> +Zuerst kommt die neue Spalte in die bestehende Tabelle:
-CREATE TABLE table_name ( +
-  id                INT AUTO_INCREMENT PRIMARY KEY, +
-  foreign_key_col   INT NOT NULL,  -- Datentyp muss zum referenzierten Primary Key (in der anderen Tabelle) passen +
-  FOREIGN KEY (foreign_key_col) REFERENCES parent_table(parent_pk) +
-); +
-</code> +
-**Wichtig:** Die **referenzierte Tabelle** (parent_table) muss **bereits existieren**, sonst meldet MySQL/MariaDB einen Fehler. +
-</WRAP>+
  
-===== Beispiel Reiseblog =====+<WRAP center box 80% round><code sql> 
 +ALTER TABLE kind_tabelle 
 +ADD COLUMN neue_spalte DATENTYP; 
 +</code></WRAP>
  
-<WRAP center tip round 80%> +Danach wird genau diese Spalte zum Fremdschlüssel:
-{{youtube>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 ====+<WRAP center box 80% round><code sql> 
 +ALTER TABLE kind_tabelle 
 +ADD CONSTRAINT fk_name 
 +FOREIGN KEY (neue_spalte) 
 +REFERENCES eltern_tabelle (primaerschluessel) 
 +ON DELETE referenzaktion; 
 +</code></WRAP>
  
-<WRAP box round center 80%> +Den //fk_name// wählen Sie selbst, zum Beispiel //fk_posts_editor//. So erkennt man bei Fehlermeldungen, welcher Constraint sich hier bemerkbar macht.
-=== Tabelle users === +
-<code sql> +
-CREATE TABLE users ( +
-  user_id       INT AUTO_INCREMENT PRIMARY KEY, +
-  username      VARCHAR(60)  NOT NULL UNIQUE, +
-  email         VARCHAR(191) NOT NULL UNIQUE, +
-  display_name  VARCHAR(100) NOT NULL, +
-  registered_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP +
-); +
-</code> +
-</WRAP>+
  
-<WRAP box round center 80%> 
-=== Tabelle posts === 
-<code sql> 
-CREATE TABLE posts ( 
-  post_id              INT AUTO_INCREMENT PRIMARY KEY, 
-  title                VARCHAR(200) NOT NULL, 
-  featured_img         VARCHAR(512), 
-  content              TEXT, 
-  created_at           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP, 
-  author_id            INT          NOT NULL, 
-  FOREIGN KEY (author_id) REFERENCES users (user_id) -- Foreign Key wird hier angelegt! 
-); 
-</code> 
-</WRAP> 
  
-<WRAP box round center 80%> +===== Die drei Referenzaktionen ===== 
-=== Tabelle categories === +Mit ''ON DELETE'' legen Sie fest, was mit den Kindzeilen passiert, wenn ihre Elternzeile gelöscht wird. 
-<code sql> + 
-CREATE TABLE categories ( +<WRAP center box 80% round> 
-  category_id INT AUTO_INCREMENT PRIMARY KEY, +^ Referenzaktion ^ Was passiert mit den Kindzeilen? ^ Beispiel ^ 
-  name        VARCHAR(100) NOT NULL, +| **RESTRICT** | Nichts. Das Löschen der Elternzeile wird verhindert. | Eine Autorin mit Posts kann nicht gelöscht werden | 
-  slug        VARCHAR(120) NOT NULL UNIQUE  -- z. B. 'schweiz', 'staedtereise' +| **CASCADE** | Sie werden mitgelöscht. | Wird ein Post gelöscht, verschwinden seine Kommentare | 
-); +| **SET NULL** | Sie bleiben, der Verweis wird leer (NULL). | Eine Redaktorin wird gelöscht, ihre Posts bleiben |
-</code>+
 </WRAP> </WRAP>
  
 +Ohne Angabe gilt **RESTRICT**. Das haben wir auf der letzten Seite gesehen, als sich Martin nicht löschen liess.
  
-==== 2. Beispieldaten einfügen ==== +<WRAP center round info 80%> 
-//Für ausführliche Erklärung zum Einfügen von Daten in Tabellen via SQL schauen Sie in "LU07 - DML: Daten einfügen, ändern und löschen" nach.//+In MySQL heisst der Standard genau genommen //NO ACTION//. Er verhält sich gleich wie RESTRICT.\\ 
 +Neben ''ON DELETE'' gibt es auch ''ON UPDATE'', das beim Ändern eines Primärschlüssels greift. Primärschlüssel werden in der Regel nie geändert, deshalb lassen wir es weg. Es gilt dann ebenfalls RESTRICT. 
 +</WRAP>
  
-<WRAP center box 80% round><code sql> +Für die Wahl hilft eine Frage: **Ergibt die Kindzeile ohne ihre Elternzeile noch Sinn?**
--- 1) Autor:innen +
-INSERT INTO users (username, email, display_name) VALUES +
-  ('caro',    'caro@wetraveltheworld.de',    'Caro Steig'), +
-  ('martin',  'martin@wetraveltheworld.de',  'Martin Merten'), +
-  ('shaolin', 'shaolin@wetraveltheworld.de', 'Shaolin Tran');+
  
--- 2) Kategorien +  * Nein, sie wäre nutzlos. Dann passt **CASCADE**. 
-INSERT INTO categories (name, slug) VALUES +  * Ja, der Verweis ist nur eine Zusatzinformation. Dann passt **SET NULL**. 
-('Städtereise', 'staedtereise'), +  * Die Elternzeile soll gar nicht gelöscht werden können, solange Kindzeilen existieren. Dann bleibt es bei **RESTRICT**.
-('Abenteuer', 'abenteuer'), +
-('Roadtrip', 'roadtrip'), +
-('Belgien', 'belgien'), +
-('Niederlande', 'niederlande'), +
-('Portugal', 'portugal'), +
-('Deutschland', 'deutschland'), +
-('Montenegro', 'montenegro'), +
-('Oman', 'oman'), +
-('USA', 'usa');+
  
--- 3) Posts 
-INSERT INTO posts (title, featured_img, content, created_at, author_id) VALUES 
-('Hasselt – 10 Highlights', 
-'https://wetraveltheworld.de/wp-content/uploads/2025/05/hasselt.jpg', 
-'Kurzguide: Die schönsten Ecken von Hasselt …', 
-'2025-05-07 10:15:00', 2),   -- Martin 
  
-('Utrecht – 10 Sehenswürdigkeiten', +===== Beispiel 1: SET NULL ===== 
-'https://wetraveltheworld.de/wp-content/uploads/2025/06/utrecht.jpg', +Jeder Post bekommt eine Redaktorin, die den Text gegenliest (//editor_id//). Das ist freiwillig. Wird diese Person gelöscht, soll der Post bleiben. Nur der Verweis fällt weg.
-'Cafés, Grachten und Restaurant-Tipps …', +
-'2025-06-05 09:30:00', 2),   -- Martin+
  
-('Lissabon – 8 Tipps zu den wichtigsten Sehenswürdigkeiten', +**Spalte ergänzen und befüllen:** 
-'https://wetraveltheworld.de/wp-content/uploads/2025/03/lissabon.jpg', +<WRAP center box 80% round><code sql> 
-'Aussichtspunkte, Viertel und Highlights …', +ALTER TABLE posts 
-'2025-03-21 08:40:00', 1),   -- Caro+ADD COLUMN editor_id INT NULL;
  
-('Maastricht an einem Tag', +UPDATE posts SET editor_id = 2 WHERE post_id = 1;  -- Hasselt: Martin liest gegen 
-'https://wetraveltheworld.de/wp-content/uploads/2025/06/maastricht.jpg', +UPDATE posts SET editor_id = 1 WHERE post_id = 2;  -- Utrecht: Caro liest gegen 
-'Spaziergang, Restaurants, Altstadt …', +UPDATE posts SET editor_id = 3 WHERE post_id = 3;  -- Lissabon: Shaolin liest gegen 
-'2025-06-12 11:05:00', 1),   -- Caro+</code></WRAP>
  
-('Montenegro Roadtrip – 10 Highlights', +Wichtig ist das ''NULL'' bei der Spalte. Ohne dieses dürfte die Spalte nie leer sein, und SET NULL könnte nicht funktionieren.
-'https://wetraveltheworld.de/wp-content/uploads/2025/06/montenegro.jpg', +
-'Bucht von Kotor, Durmitor, Tara-Schlucht …', +
-'2025-06-11 14:22:00', 1),   -- Caro+
  
-('Oman – Top 22 Highlights', +**Fremdschlüssel setzen:** 
-'https://wetraveltheworld.de/wp-content/uploads/2025/06/oman.jpg', +<WRAP center box 80% round><code sql> 
-'Nizwa, Wadis, Wüste und Roadtrip-Tipps …', +ALTER TABLE posts 
-'2025-06-11 09:00:00', 1),   -- Caro+ADD CONSTRAINT fk_posts_editor 
 +FOREIGN KEY (editor_id) 
 +REFERENCES users (user_id) 
 +ON DELETE SET NULL; 
 +</code></WRAP>
  
-('Chicago in 3 Tagen – 17 Highlights', +**Testen:** 
-'https://wetraveltheworld.de/wp-content/uploads/2024/10/chicago.jpg', +<WRAP center box 80% round><code sql> 
-'Riverwalk, The Bean, Museen und Skyline …', +DELETE FROM users WHERE username = 'shaolin';
-'2024-10-07 07:50:00', 2);   -- Martin+
  
 +SELECT post_id, title, editor_id FROM posts ORDER BY post_id;
 </code></WRAP> </code></WRAP>
  
-**Resultate (nach dem Einfügen):**+Shaolin ist gelöscht. Den Lissabon-Post gibt es weiterhin, seine //editor_id// ist jetzt NULL.
  
-=== Verknüpfung Tabelle users & posts (one-to-many) ===+<WRAP center round tip 80%> 
 +Shaolin liess sich löschen, weil er bei keinem Post **Autor** ist. Der Fremdschlüssel //author_id// blockiert also nichts. Bei Martin sieht das anders aus: Er ist Autor von drei Posts, und //author_id// steht weiterhin auf RESTRICT. Martin lässt sich deshalb immer noch nicht löschen. 
 +</WRAP>
  
-{{de:modul:m290_guko:learningunits:lu08:theorie:reiseblog-connected_users-posts.png?direct&1600|}} 
  
-=== Tabelle categories (wird später mit 'posts' verknüpft) ===+===== Beispiel 2: CASCADE ===== 
 +Jetzt kommen Kommentare dazu. Jeder Kommentar gehört zu genau einem Post. Ohne seinen Post ergibt ein Kommentar keinen Sinn. Wird der Post gelöscht, sollen seine Kommentare deshalb automatisch mitgehen.
  
-{{de:modul:m290_guko:learningunits:lu08:theorie:categories.png?direct&400|}} +**Tabelle anlegen:**
- +
-==== 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'': +
- +
-  * 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) ===+
 <WRAP center box 80% round><code sql> <WRAP center box 80% round><code sql>
-DELETE FROM users +CREATE TABLE comments ( 
-WHERE username = 'shaolin'; -- hat keine Posts zugewiesen +  comment_id INT AUTO_INCREMENT PRIMARY KEY, 
-SELECT user_id, username FROM users;+  post_id    INT NOT NULL, 
 +  author     VARCHAR(100) NOT NULL, 
 +  body       TEXT NOT NULL, 
 +  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
 +  FOREIGN KEY (post_id) REFERENCES posts (post_id) 
 +    ON DELETE CASCADE 
 +);
 </code></WRAP> </code></WRAP>
-//Erwartung: Die Zeile wird gelöscht (keine Posts verweisen auf den User).// 
  
-=== Demo 2 – User mit Posts löschen (blockiert) === +Hier steht der Fremdschlüssel direkt im CREATE TABLE, wie auf der letzten Seite. Eine Referenzaktion lässt sich auf beiden Wegen angeben.
-<WRAP center box 80% round><code sql> +
-DELETE FROM users +
-WHERE username = 'martin'; +
-</code></WRAP> +
-//Erwartete Fehlermeldung (sinngemäss):// +
-<WRAP alert round 80% center> +
-[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)) +
-</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) ===+**Befüllen:**
 <WRAP center box 80% round><code sql> <WRAP center box 80% round><code sql>
-UPDATE users +INSERT INTO comments (post_id, author, body) VALUES 
-SET username = 'martin_travels' +  (2, 'Leser A',     'Toller Utrecht-Tipp!'), 
-WHERE user_id = 2; -- OK: FKs verweisen auf user_id, nicht auf username+  (2, 'Leserin B',   'Gute Café-Empfehlungen.'), 
 +  (5, 'Reisefreund', 'Kotor war mein Highlight!');
 </code></WRAP> </code></WRAP>
  
-=== Demo 4 – Primärschlüssel ändern (blockiert) ===+**Testen:**
 <WRAP center box 80% round><code sql> <WRAP center box 80% round><code sql>
-UPDATE users +DELETE FROM posts WHERE post_id = 2;
-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) === +SELECT * FROM comments;
-<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> </code></WRAP>
  
-=== Warum ist das so? ===+Der Utrecht-Post ist weg, und mit ihm seine beiden Kommentare. Übrig bleibt nur der Kommentar zum Montenegro-Post.
  
-//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.+<WRAP center round important 80%> 
 +CASCADE ist bequem, aber mit Vorsicht zu geniessen. Ein einziger DELETE kann so viele Zeilen in anderen Tabellen mitlöschen. Verwenden Sie es nur dort, wo die Kindzeilen ohne Elternzeile wirklich nutzlos sind. 
 +</WRAP>
  
-Ä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.+===== Zusammenfassung =====
  
-<WRAP tip round 80% center> +<WRAP center box 80% round> 
-Merke: Fremdschlüssel geben Daten­sicherheit: +^ Referenzaktion ^ Wann einsetzen? ^ 
-  * Verhindern verwaiste Daten (z. B. Posts ohne gültigen Autor), +| RESTRICT | Standard. Die Elternzeile soll nicht gelöscht werden, solange Kindzeilen darauf verweisen. | 
-  * definieren klares Verhalten bei Löschen/Ändern (RESTRICT, CASCADE, SET NULL), +| CASCADE | Die Kindzeilen sind ohne Elternzeile nutzlos, zum Beispiel Kommentare. | 
-  * halten die Datenbank konsistent.+| SET NULL | Der Verweis ist freiwillig und die Kindzeile soll bleiben. |
 </WRAP> </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>+ 
 +<WRAP center round box 80%> 
 +{{youtube>aaO2cUhN9zA?}}\\ 
 +//ON DELETE: NO ACTION, SET NULL, CASCADE, SET DEFAULT (9:22 min, deutsch). Quelle: Prof. Dr. Jens Dittrich / YouTube\\ 
 +Das Video zeigt zusätzlich SET DEFAULT. Diese Option brauchen wir im Modul nicht.// 
 +</WRAP> 
 + 
 +---- 
 +{{tag>M290-LU08}} 
 +[[https://creativecommons.org/licenses/by-nc-sa/4.0/|{{https://i.creativecommons.org/l/by-nc-sa/4.0/88x31.png}}]] Guido Koch
  
  • de/modul/m290_guko/learningunits/lu08/theorie/c_fk-create-table.1792235874.txt.gz
  • Zuletzt geändert: 2026/10/17 13:17
  • von gkoch