de:modul:m290_guko:learningunits:lu08:theorie:d_fk-alter-table

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
de:modul:m290_guko:learningunits:lu08:theorie:d_fk-alter-table [2026/10/17 13:14] – gkochde:modul:m290_guko:learningunits:lu08:theorie:d_fk-alter-table [2026/10/17 16:42] (aktuell) – gkoch
Zeile 1: Zeile 1:
-====== LU08d: FKs per ALTER TABLE + Referenzaktionen ======+====== LU08d - n:m-Beziehungen mit einer Zwischentabelle ======
  
-**Ziel:** Bereits bestehende Tabellen mit **ALTER TABLE** um Fremdschlüssel ergänzen und verstehen, was **RESTRICT**, **CASCADE** und **SET NULL** bewirken.+===== Lernziele ===== 
 +  * Ich kann erklären, warum sich eine n:m-Beziehung nicht mit einem einzelnen Fremdschlüssel abbilden lässt. 
 +  * Ich kann eine Zwischentabelle mit zusammengesetztem Primärschlüssel erstellen und darin Zuordnungen einfügen.
  
-**Voraussetzung:** Die Codebeispiele verwenden die Tabellen **users**, **posts**, **categories**, **post_category** inkl. Beispieldaten aus der vorherigen Seite. 
  
 +===== Ausgangslage =====
 +Ein Post kann zu **mehreren Kategorien** gehören. Der Utrecht-Post zum Beispiel zu Niederlande und Städtereise. Gleichzeitig enthält eine Kategorie **mehrere Posts**. Das ist eine **n:m-Beziehung**.
  
-===== 0) ALTER TABLE – Spalte in bestehende Tabelle hinzufügen ===== +<WRAP center round tip 80%> 
-Mit **ALTER TABLE** können bestehende Tabellen geändert werden – also auch Fremdschlüssel hinzugefügt werden.+Im ÖV-Case aus LU05 gab es das schon einmal. Zwischen //linie// und //haltestelle// standen im Crow's-Foot-Diagramm Krähenfüsse auf **beiden** Seiten, und es gab keinen Fremdschlüssel. Damals hiess es, dass wir das später mit einer Zwischentabelle lösen. Hier lernen Sie, wie das geht. 
 +</WRAP>
  
-<WRAP box round center 80%> +Zuerst braucht der Reiseblog eine Tabelle für die Kategorien:
-**Spalte hinzufügen (generelle Syntax)** +
-<code sql> +
-ALTER TABLE table_name +
-ADD COLUMN neue_spalte DATENTYP [AFTER bestehende_spalte]; +
-</code>+
  
-**Fremdschlüssel hinzufügen** +<WRAP center box 80% round><code sql> 
-<code sql> +CREATE TABLE categories ( 
-ALTER TABLE table_name +  category_id INT AUTO_INCREMENT PRIMARY KEY, 
-ADD CONSTRAINT fk_name +  name        VARCHAR(100) NOT NULL, 
-FOREIGN KEY (neue_spalte) +  slug        VARCHAR(120) NOT NULL UNIQUE 
-REFERENCES parent_table(parent_pk) +); 
-ON DELETE RESTRICT|CASCADE|SET NULL + 
-ON UPDATE RESTRICT|CASCADE|SET NULL; +INSERT INTO categories (name, slug) VALUES 
-</code> +  ('Städtereise', 'staedtereise'), ('Abenteuer', 'abenteuer'), 
-</WRAP>+  ('Roadtrip', 'roadtrip'),        ('Belgien', 'belgien'), 
 +  ('Niederlande', 'niederlande'),  ('Portugal', 'portugal'), 
 +  ('Deutschland', 'deutschland'),  ('Montenegro', 'montenegro'), 
 +  ('Oman', 'oman'),                ('USA', 'usa'); 
 +</code></WRAP>
  
-<WRAP center tip round 80%> +<WRAP center round info 80%> 
-{{youtube>aaO2cUhN9zA?}} +Der **Slug** ist die Version eines Namens, die sich für eine Webadresse eignet: ohne Leerzeichen, Umlaute und Grossbuchstaben. Aus "Städtereise" wird "staedtereise", zum Beispiel in //wetraveltheworld.de/category/staedtereise//. Aus WordPress kennen Sie das vielleicht schon.
-//ON DELETE: NO ACTION, SET NULL, CASCADE, SET DEFAULT((Prof. Dr. Jens Dittrich – Big Data Analytics / YouTube)) -> (9:22, de) Referenzaktionen kompakt: was bei Löschen/Ändern passiert und wann welche Option sinnvoll ist.//+
 </WRAP> </WRAP>
  
-===== 1) SET NULL: Redaktor:in (editor_id) in posts ===== +{{ :de:modul:m290_guko:learningunits:lu08:theorie:categories.png?direct&400 |Tabelle categories}}
-Wir ergänzen in **posts** eine **optionale** verantwortliche Redaktor:in (**editor_id**) und verknüpfen sie mit **users**.+
  
-**1.1 Spalte ergänzen und Beispielwerte setzen** 
-<WRAP box round center 80%> 
-<code sql> 
--- Spalte hinzufügen 
-ALTER TABLE posts 
-ADD COLUMN editor_id INT NULL AFTER author_id; 
  
--- Shaolin wieder hinzufügen, falls gelöscht +===== Warum ein Fremdschlüssel nicht reicht ===== 
-INSERT INTO users (username, email, display_name) +Aus LU04 kennen Sie die Regel **Eine Zelle, ein Wert.** Ein Fremdschlüssel kann deshalb immer nur auf **eine** Zeile verweisen. Bei n:m bräuchten aber **beide** Seiten mehrere Verweise. Jeder naheliegende Versuch scheitert:
-VALUES('shaolin','shaolin@wetraveltheworld.de','Shaolin Tran');+
  
--- Beispielwerte passend zu user_id: 1=caro, 2=martin, 3=shaolin +<WRAP center box 80% round> 
-UPDATE posts SET editor_id = 2 WHERE post_id = 1;  -- Post #1: Editor = martin +^ Versuch ^ Problem ^ 
-UPDATE posts SET editor_id = 1 WHERE post_id = 2;  -- Post #2: Editor = caro +| //category_id// in //posts// | Pro Post ist nur **eine** Kategorie möglich | 
-UPDATE posts SET editor_id = 4 WHERE post_id = 3;  -- Post #3: Editor = shaolin (wenn Shaolin bereits gelöscht wurde und neu hier hinzugefügt wurde, dann hat er jetzt user_id 4) +| //post_id// in //categories// | Pro Kategorie ist nur **ein** Post möglich | 
-</code>+| Kommaliste ''1, 5'' in einer Spalte | Mehrere Werte in einer Zelle. Keine Prüfung, unzuverlässige Abfragen. | 
 +| Spalten //category_id1//, //category_id2// usw. | Die Anzahl ist begrenzt. Braucht ein Post mehr Kategorien, muss die Tabelle umgebaut werden. |
 </WRAP> </WRAP>
  
-**1.2 Fremdschlüssel setzen – SET NULL beim Löschen** +{{ :de:modul:m290_guko:learningunits:lu08:theorie:many-to-many_not-resolved.png?direct&800 |Ungeeignete Lösungsversuche für n:m}}
-<WRAP box round center 80%> +
-<code sql> +
-ALTER TABLE posts +
-ADD CONSTRAINT fk_posts_editor +
-FOREIGN KEY (editor_id) +
-REFERENCES users (user_id) +
-ON DELETE SET NULL     -- User gelöscht → Post bleibt, Verweis wird NULL +
-ON UPDATE RESTRICT;    -- Primärschlüssel von users bleibt stabil +
-</code> +
-</WRAP>+
  
-<WRAP tip round 80% center> 
-**Warum SET NULL?** Die Redaktor:in ist **optional**. Wird der zugehörige User gelöscht, soll der Post **nicht** verschwinden – der optionale Verweis fällt auf **NULL**. 
-</WRAP> 
  
-**Test:** +===== Die Zwischentabelle ===== 
-<WRAP center box 80% round> +//posts// und //categories// bleiben, wie sie sind. Dazu kommt eine **dritte Tabelle**: //post_category//. Jede Zeile darin speichert **genau eine Zuordnung**, also ein Paar aus //post_id// und //category_id//.
-<code sql> +
--- Lösche User 'shaolin' (ist nur Editor, nicht Autor) +
-DELETE FROM users WHERE username = 'shaolin';+
  
--- Kontrolle: editor_id des betroffenen Posts ist jetzt NULL +Gehört der Utrecht-Post zu zwei Kategorien, gibt es dafür zwei Zeilen. In jeder Zelle steht so wieder genau ein Wert.
-SELECT post_id, title, editor_id FROM posts ORDER BY post_id; +
-</code> +
-</WRAP>+
  
 +{{ :de:modul:m290_guko:learningunits:lu08:theorie:reiseblog-connected_posts-categories.png?direct&1600 |posts und categories über die Zwischentabelle post_category}}
  
 +Aus der n:m-Beziehung werden damit **zwei 1:n-Beziehungen**. Ein Post hat viele Zuordnungen, und eine Kategorie hat viele Zuordnungen. Die Fremdschlüssel stehen wie immer auf der "viele"-Seite. Hier ist das in beiden Fällen die Zwischentabelle.
  
-===== 2) CASCADE: Kommentare zur posts (comments → posts) ===== 
-Wir fügen eine Kindtabelle **comments** hinzu. Jeder Kommentar gehört zu **genau einem** Post. Wird ein Post gelöscht, sollen die zugehörigen Kommentare **automatisch verschwinden**. 
  
-**2.1 Tabelle anlegen (mit CASCADE)** +===== Zwischentabelle anlegen ===== 
-<WRAP box round center 80%> + 
-<code sql> +<WRAP center box 80% round><code sql> 
-CREATE TABLE comments ( +CREATE TABLE post_category ( 
-comment_id  INT AUTO_INCREMENT PRIMARY KEY, +  post_id     INT NOT NULL, 
-post_id     INT NOT NULL, +  category_id INT NOT NULL, 
-author      VARCHAR(100) NOT NULL, +  PRIMARY KEY (post_id, category_id), 
-body        TEXT NOT NULL, +  FOREIGN KEY (post_id) REFERENCES posts (post_id) 
-created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, +    ON DELETE CASCADE, 
-FOREIGN KEY (post_id) +  FOREIGN KEY (category_id) REFERENCES categories (category_id) 
-REFERENCES posts (post_id) +    ON DELETE CASCADE
-ON DELETE CASCADE      -- Post gelöscht → zugehörige Kommentare automatisch löschen +
-ON UPDATE CASCADE      -- (optional) ändert sich die post_id, wird sie hier mitgeändert+
 ); );
-</code> +</code></WRAP>
-</WRAP>+
  
-**2.2 Kurz befüllen** +Zwei Dinge sind neu.
-<WRAP box round center 80%> +
-<code sql> +
-INSERT INTO comments (post_id, author, body, created_at) VALUES +
-(2, 'Leser A',    'Toller Utrecht-Tipp!', '2025-06-05 11:00:00'), +
-(2, 'Leserin B',  'Gute Café-Empfehlungen.', '2025-06-05 12:10:00'), +
-(5, 'Reisefreund','Kotor war mein Highlight!', '2025-06-11 16:00:00'); +
-</code> +
-</WRAP>+
  
-**2.3 Test (CASCADE in Aktion)** +**Der zusammengesetzte Primärschlüssel.** ''PRIMARY KEY (post_id, category_id)'' bedeutet, dass die **Kombination** der beiden Spalten eindeutig sein muss. Post 2 darf mehrmals vorkommen, Kategorie 1 ebenfalls. Das Paar (2, 1) gibt es aber nur einmal. Derselbe Post lässt sich also nicht zweimal derselben Kategorie zuordnen.
-<WRAP box round center 80%> <code sql> +
--- Post #2 löschen … +
-DELETE FROM posts WHERE post_id = 2;+
  
--- … die zugehörigen Kommentare sind automatisch weg: +**CASCADE auf beiden Seiten.** Das kennen Sie von der letzten Seite. Eine Zuordnung ohne Post oder ohne Kategorie ist nutzlos. Wird eines der beiden gelöscht, verschwindet die Zuordnung automatisch.
-SELECT * FROM comments WHERE post_id = 2;   -- → keine Zeilen +
-</code> +
-</WRAP>+
  
-<WRAP tip round 80% center> + 
-**Warum hier CASCADE?** Kommentare ohne zugehörigen Post sind nutzlos. +===== Zuordnungen einfügen ===== 
-Mit **ON DELETE CASCADE** bleibt die Datenbank **konsistent** und **aufräumen** passiert automatisch.+ 
 +<WRAP center box 80% round><code sql> 
 +INSERT INTO post_category (post_id, category_id) VALUES 
 +  (1, 4),  -- Hasselt → Belgien 
 +  (1, 1),  -- Hasselt → Städtereise 
 +  (2, 5),  -- Utrecht → Niederlande 
 +  (2, 1),  -- Utrecht → Städtereise 
 +  (3, 6),  -- Lissabon → Portugal 
 +  (3, 1),  -- Lissabon → Städtereise 
 +  (4, 5),  -- Maastricht → Niederlande 
 +  (4, 1),  -- Maastricht → Städtereise 
 +  (5, 8),  -- Montenegro → Montenegro 
 +  (5, 2),  -- Montenegro → Abenteuer 
 +  (5, 3),  -- Montenegro → Roadtrip 
 +  (6, 9),  -- Oman → Oman 
 +  (6, 2),  -- Oman → Abenteuer 
 +  (6, 3),  -- Oman → Roadtrip 
 +  (7, 10), -- Chicago → USA 
 +  (7, 1);  -- Chicago → Städtereise 
 +</code></WRAP> 
 + 
 +Der Montenegro-Post hat drei Kategorien und deshalb drei Zeilen. Wie viele Kategorien ein Post haben kann, ist nicht begrenzt. 
 + 
 +Die Zwischentabelle schützt sich selbst gegen Fehler: 
 + 
 +<WRAP center box 80% round> 
 +^ Befehl ^ Ergebnis ^ Warum ^ 
 +| ''INSERT INTO post_category VALUES (1, 4);'' | blockiert | Das Paar gibt es schon (Primärschlüssel) | 
 +| ''INSERT INTO post_category VALUES (1, 99);'' | blockiert | Eine Kategorie 99 gibt es nicht (Fremdschlüssel) | 
 +| ''DELETE FROM posts WHERE post_id = 2;'' | erlaubt | Die beiden Zuordnungen des Utrecht-Posts werden mitgelöscht (CASCADE). Die Kategorien selbst bleiben. |
 </WRAP> </WRAP>
  
  
 +===== Zusammenfassung =====
 +Eine n:m-Beziehung lässt sich nie mit einem einzelnen Fremdschlüssel abbilden, weil in einer Zelle nur ein Wert Platz hat. Die Lösung ist eine Zwischentabelle mit einer Zuordnung pro Zeile. Ihr Primärschlüssel setzt sich aus den beiden Fremdschlüsseln zusammen, und meistens verwendet sie CASCADE.
  
-===== 3) Zusammenfassung =====+<WRAP center round box 80%> 
 +//Zur Vertiefung: [[https://www.youtube.com/watch?v=4q-keGvUnag|One-to-many and many-to-many table relationships]] (9:50 min, englisch, mit automatischen Untertiteln). Quelle: LinkedIn Learning / YouTube// 
 +</WRAP> 
 + 
 +<WRAP center round info 80%> 
 +Die Daten liegen jetzt sauber verteilt in mehreren Tabellen. Mit **JOIN** führen wir sie in einer einzigen Abfrage wieder zusammen, zum Beispiel für "alle Posts mit Autorin und Kategorien". 
 +</WRAP>
  
-  * **RESTRICT**: Löschen/Ändern der Elternzeile **nur** möglich, wenn **keine** Kindzeilen verweisen (Standardverhalten; schützt Datenkonsistenz). +---- 
-  * **CASCADE**: Kindzeilen werden bei Änderungen/Löschungen der Eltern **automatisch** mitgeändert oder gelöscht (optimal für Zwischentabellen). +{{tag>M290-LU08}} 
-  * **SET NULL**: Kindzeile bleibt, der **optionale** Verweis wird **NULL**.+[[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/d_fk-alter-table.txt
  • Zuletzt geändert: 2026/10/17 16:42
  • von gkoch