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
Nächste Überarbeitung		 Vorhergehende Überarbeitung
modul:m290_guko:learningunits:lu08:theorie:d_fk-alter-table [2025/10/17 01:26] – gkochmodul:m290_guko:learningunits:lu08:theorie:d_fk-alter-table [2025/10/27 07:23] (aktuell) – gkoch
Zeile 1: Zeile 1:
-====== LU08d – FKs per ALTER TABLE + Referenzaktionen ======+====== LU08d: FKs per ALTER TABLE + Referenzaktionen ======
  
-**Ziel:** Bereits bestehende Tabellen mit **ALTER TABLE** um Fremdschlüssel erweitern und verstehen, was **RESTRICT**, **CASCADE** und **SET NULL** bewirken.+**Ziel:** Bereits bestehende Tabellen mit **ALTER TABLE** um Fremdschlüssel ergänzen und verstehen, was **RESTRICT**, **CASCADE** und **SET NULL** bewirken.
  
-Voraussetzung: Du verwendest die DB aus **LU08c** (Tabellen **users**, **posts**, **categories**, **post_category** mit Beispieldaten).+**Voraussetzung:** Die Codebeispiele verwenden die Tabellen **users**, **posts**, **categories**, **post_category** inkl. Beispieldaten aus der vorherigen Seite.
  
  
-===== 0) ALTER TABLE – Überblick (Syntax) ===== +===== 0) ALTER TABLE – Spalte in bestehende Tabelle hinzufügen ===== 
-Mit **ALTER TABLE** können wir bestehende Tabellen ändern.+Mit **ALTER TABLE** können bestehende Tabellen geändert werden – also auch Fremdschlüssel hinzugefügt werden.
  
 <WRAP box round center 80%> <WRAP box round center 80%>
-**Spalte hinzufügen**+**Spalte hinzufügen (generelle Syntax)**
 <code sql> <code sql>
 ALTER TABLE table_name ALTER TABLE table_name
-  ADD COLUMN neue_spalte DATENTYP [NULL|NOT NULL] [DEFAULT ...] [AFTER bestehende_spalte]; +ADD COLUMN neue_spalte DATENTYP [AFTER bestehende_spalte];
-</code> +
- +
-**Spalte umbenennen** +
-<code sql> +
-ALTER TABLE table_name +
-  RENAME COLUMN altname TO neuename; +
-</code> +
- +
-**Datentyp ändern** +
-<code sql> +
-ALTER TABLE table_name +
-  MODIFY COLUMN spalte NEUER_DATENTYP [NULL|NOT NULL] [DEFAULT ...]; +
-</code> +
- +
-**Spalte löschen** +
-<code sql> +
-ALTER TABLE table_name +
-  DROP COLUMN spalte;+
 </code> </code>
  
 **Fremdschlüssel hinzufügen** **Fremdschlüssel hinzufügen**
-<code sql> 
-ALTER TABLE child_table 
-  ADD CONSTRAINT fk_name 
-    FOREIGN KEY (fk_spalte) 
-    REFERENCES parent_table(parent_pk) 
-    [ON DELETE {RESTRICT|CASCADE|SET NULL}] 
-    [ON UPDATE {RESTRICT|CASCADE|SET NULL}]; 
-</code> 
- 
-**Fremdschlüssel/Constraint löschen** 
 <code sql> <code sql>
 ALTER TABLE table_name ALTER TABLE table_name
-  DROP FOREIGN KEY fk_name; +ADD CONSTRAINT fk_name 
--- Namen via +FOREIGN KEY (neue_spalte) 
-SHOW CREATE TABLE table_name; +REFERENCES parent_table(parent_pk) 
--- herausfinden.+ON DELETE RESTRICT|CASCADE|SET NULL 
 +ON UPDATE RESTRICT|CASCADE|SET NULL;
 </code> </code>
 </WRAP> </WRAP>
  
-----+<WRAP center tip round 80%> 
 +[[https://www.youtube.com/watch?v=aaO2cUhN9zA|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>
  
-===== 1) Optionalen Verweis in POSTS ergänzen (featured_category_id) ===== +===== 1) SET NULL: Redaktor:in (editor_id) in posts ===== 
-Wir ergänzen eine optionale Hauptkategorie im **posts**-Tabelle und verknüpfen sie mit **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%> <WRAP box round center 80%>
-=== 1.1 Spalte ergänzen und Beispielwerte setzen === 
 <code sql> <code sql>
--- Spalte hinzufügen (optional, falls noch nicht vorhanden)+-- Spalte hinzufügen
 ALTER TABLE posts ALTER TABLE posts
-  ADD COLUMN featured_category_id INT NULL AFTER content;+ADD COLUMN editor_id INT NULL AFTER author_id;
  
--- Beispielwerte passend zu LU08c-Daten +-- Shaolin wieder hinzufügen, falls gelöscht 
-UPDATE posts SET featured_category_id = 1 WHERE post_id = 1;  -- Schweiz +INSERT INTO users (username, email, display_name) 
-UPDATE posts SET featured_category_id = 2 WHERE post_id = 2;  -- Städtereise +VALUES('shaolin','shaolin@wetraveltheworld.de','Shaolin Tran'); 
-UPDATE posts SET featured_category_id = 3 WHERE post_id = 3;  -- Abenteuer+ 
 +-- Beispielwerte passend zu user_id: 1=caro, 2=martin, 3=shaolin 
 +UPDATE posts SET editor_id = 2 WHERE post_id = 1;  -- Post #1: Editor = martin 
 +UPDATE posts SET editor_id = 1 WHERE post_id = 2;  -- Post #2: Editor = caro 
 +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)
 </code> </code>
 </WRAP> </WRAP>
  
 +**1.2 Fremdschlüssel setzen – SET NULL beim Löschen**
 <WRAP box round center 80%> <WRAP box round center 80%>
-=== 1.2 Fremdschlüssel setzen (SET NULL beim Löschen) === 
 <code sql> <code sql>
 ALTER TABLE posts ALTER TABLE posts
-  ADD CONSTRAINT fk_posts_featured_category +ADD CONSTRAINT fk_posts_editor 
-    FOREIGN KEY (featured_category_id) +FOREIGN KEY (editor_id) 
-    REFERENCES categories (category_id) +REFERENCES users (user_id) 
-    ON DELETE SET NULL     -- Kategorie gelöscht → Post bleibt, Verweis wird NULL +ON DELETE SET NULL     -- User gelöscht → Post bleibt, Verweis wird NULL 
-    ON UPDATE RESTRICT;    -- Kategorie-ID darf nicht "umgehängt" werden+ON UPDATE RESTRICT;    -- Primärschlüssel von users bleibt stabil
 </code> </code>
 </WRAP> </WRAP>
  
-<WRAP tip round 80%> +<WRAP tip round 80% center> 
-**Warum SET NULL?** Die Hauptkategorie ist **optional**. Wird eine Kategorie gelöscht, soll der Post **nicht** verschwinden – nur der Verweis fällt auf **NULL**.+**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> </WRAP>
  
----- +**Test:** 
- +<WRAP center box 80% round>
-===== 2) N:M-Zuordnung absichern (post_category mit CASCADE) ===== +
-Die Zuordnungstabelle **post_category (post_id, category_id)** soll sich **automatisch aufräumen**, wenn ein Post oder eine Kategorie gelöscht wird. +
- +
-<WRAP box round center 80%> +
-=== 2.1 FKs mit CASCADE hinzufügen (oder neu setzen) ===+
 <code sql> <code sql>
--- Falls die FKs noch NICHT existieren: +-- Lösche User 'shaolin' (ist nur Editor, nicht Autor) 
-ALTER TABLE post_category +DELETE FROM users WHERE username = 'shaolin';
-  ADD CONSTRAINT fk_pc_post +
-    FOREIGN KEY (post_id) +
-    REFERENCES posts (post_id) +
-    ON DELETE CASCADE +
-    ON UPDATE CASCADE, +
-  ADD CONSTRAINT fk_pc_category +
-    FOREIGN KEY (category_id) +
-    REFERENCES categories (category_id) +
-    ON DELETE CASCADE +
-    ON UPDATE CASCADE;+
  
--- Falls bereits FKs ohne CASCADE existieren: +-- Kontrolle: editor_id des betroffenen Posts ist jetzt NULL 
--- 1) Namen ermitteln +SELECT post_id, title, editor_id FROM posts ORDER BY post_id;
-SHOW CREATE TABLE post_category; +
--- 2) Alte FKs löschen +
-ALTER TABLE post_category +
-  DROP FOREIGN KEY <alter_fk_post>, +
-  DROP FOREIGN KEY <alter_fk_category>; +
--- 3) Obige ALTER-Anweisung erneut ausführen (mit CASCADE)+
 </code> </code>
 </WRAP> </WRAP>
  
----- 
  
-===== 3) Referenzaktionen – kurz & praxisnah ===== 
  
 +===== 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)**
 <WRAP box round center 80%> <WRAP box round center 80%>
-**RESTRICT** – schützt Elternzeilen   
-Eltern (z. B. **users**) können nicht gelöscht/aktualisiert werden, solange Kinder (z. B. **posts**) auf sie verweisen. 
- 
-**Demo (soll fehlschlagen):** 
 <code sql> <code sql>
-DELETE FROM users WHERE username = 'caro';  -- erwartet: Fehler (RESTRICT)+CREATE TABLE comments ( 
 +comment_id  INT AUTO_INCREMENT PRIMARY KEY, 
 +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      -- 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**
 <WRAP box round center 80%> <WRAP box round center 80%>
-**CASCADE** – räumt automatisch auf   
-Kindzeilen in **post_category** werden mitgelöscht, wenn der zugehörige Post/Kategorie verschwindet. 
- 
-**Demo:** 
 <code sql> <code sql>
--- Post #2 löschen → zugehörige Mappings verschwinden automatisch +INSERT INTO comments (post_id, author, body, created_at) VALUES 
-DELETE FROM posts WHERE post_id = 2; +(2, 'Leser A',    'Toller Utrecht-Tipp!', '2025-06-05 11:00:00'), 
- +(2, 'Leserin B',  'Gute Café-Empfehlungen.', '2025-06-05 12:10:00'), 
--- Prüfung: +(5, 'Reisefreund','Kotor war mein Highlight!', '2025-06-11 16:00:00');
-SELECT * FROM post_category WHERE post_id = 2;  -- → keine Zeilen+
 </code> </code>
 </WRAP> </WRAP>
  
-<WRAP box round center 80%> +**2.3 Test (CASCADE in Aktion)** 
-**SET NULL** – Verweis wird auf NULL gesetzt   +<WRAP box round center 80%> <code sql> 
-Kindzeile (hier: **posts**) bleibt erhalten, der optionale Verweis wird NULL. +-- Post #2 löschen … 
- +DELETE FROM posts WHERE post_id = 2;
-**Demo:** +
-<code sql> +
--- Kategorie 'staedtereise' löschen (id=2) +
-DELETE FROM categories WHERE slug = 'staedtereise';+
  
--- featured_category_id wird NULL, Post bleibt bestehen +-- … die zugehörigen Kommentare sind automatisch weg: 
-SELECT post_id, title, featured_category_id +SELECT * FROM comments WHERE post_id = 2;   -- → keine Zeilen
-FROM posts +
-ORDER BY post_id;+
 </code> </code>
 </WRAP> </WRAP>
  
-**Beispielresultat (Ausschnitt nach den Demos):** +<WRAP tip round 80% center> 
-<WRAP center box 80% round> +**Warum hier CASCADE?** Kommentare ohne zugehörigen Post sind nutzlos. 
-^ post_id ^ title                                               ^ featured_category_id ^ +Mit **ON DELETE CASCADE** bleibt die Datenbank **konsistent** und **aufräumen** passiert automatisch.
-| 1 | Die Schweiz ist zu teuer? … Davos & Klosters im Winter | 1    | +
-| 3 | Usbekistan Rundreise Highlights – Die 12 besten …      | 3    |+
 </WRAP> </WRAP>
-<wrap lo>Hinweis: Durch das Löschen von Post #2 (CASCADE) und Kategorie „Städtereise“ (SET NULL) sind die entsprechenden Verknüpfungen bereinigt bzw. auf NULL gesetzt.</wrap> 
  
----- 
  
-===== 4) Merksätze ===== 
-* **RESTRICT**: Löschen/Ändern der Elternzeile nur erlaubt, wenn **keine** Kindzeilen existieren.   
-* **CASCADE**: Löscht/aktualisiert zugehörige Kindzeilen **automatisch** (ideal für N:M-Zuordnungen).   
-* **SET NULL**: Kindzeile bleibt erhalten, FK-Spalte wird **NULL** (ideal für **optionale** Verweise). 
  
-<WRAP tip round 80%> +===== 3) Zusammenfassung =====
-**Reihenfolge beim Nachrüsten von FKs:**   +
-1) **Daten prüfen/bereinigen** (keine verwaisten Verweise).   +
-2) **ALTER TABLE** mit passenden Referenzaktionen setzen.   +
-3) Verhalten mit kleinen Test-Löschungen/-Änderungen nachvollziehen. +
-</WRAP>+
  
-<WRAP info round 80%> +  * **RESTRICT**: Löschen/Ändern der Elternzeile **nur** möglich, wenn **keine** Kindzeilen verweisen (Standardverhalten; schützt Datenkonsistenz). 
-**Ausblick (LU09):** Mehrtabellen-Abfragen mit **JOINs** und **Subqueries** auf unserem Reiseblog-Schema. +  * **CASCADE**: Kindzeilen werden bei Änderungen/Löschungen der Eltern **automatisch** mitgeändert oder gelöscht (optimal für Zwischentabellen). 
-</WRAP>+  * **SET NULL**: Kindzeile bleibt, der **optionale** Verweis wird **NULL**.
  
  • modul/m290_guko/learningunits/lu08/theorie/d_fk-alter-table.1760657180.txt.gz
  • Zuletzt geändert: 2025/10/17 01:26
  • von gkoch