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:34] – 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>
  
Zeile 37: Zeile 19:
 <code sql> <code sql>
 ALTER TABLE table_name ALTER TABLE table_name
-  ADD CONSTRAINT fk_name +ADD CONSTRAINT fk_name 
-    FOREIGN KEY (fk_spalte) +FOREIGN KEY (neue_spalte) 
-    REFERENCES parent_table(parent_pk) +REFERENCES parent_table(parent_pk) 
-    [ON DELETE {RESTRICT|CASCADE|SET NULL}] +ON DELETE RESTRICT|CASCADE|SET NULL 
-    [ON UPDATE {RESTRICT|CASCADE|SET NULL}];+ON UPDATE RESTRICT|CASCADE|SET NULL;
 </code> </code>
 +</WRAP>
  
-**Fremdschlüssel/Constraint löschen** +<WRAP center tip round 80%> 
-<code sql> +[[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.
-ALTER TABLE table_name +
-  DROP FOREIGN KEY fk_name; +
--- Namen via +
-SHOW CREATE TABLE table_name; +
--- herausfinden. +
-</code>+
 </WRAP> </WRAP>
  
- +===== 1) SET NULL: Redaktor:in (editor_id) in posts =====
-===== 1) Optionalen Verweis in POSTS ergänzen =====+
 Wir ergänzen in **posts** eine **optionale** verantwortliche Redaktor:in (**editor_id**) und verknüpfen sie mit **users**. 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 -- Spalte hinzufügen
 ALTER TABLE posts ALTER TABLE posts
-  ADD COLUMN editor_id INT NULL AFTER author_id;+ADD COLUMN editor_id INT NULL AFTER author_id;
  
--- Beispielwerte passend zu LU08c-Daten (user_id: 1=caro, 2=martin, 3=shaolin)+-- Shaolin wieder hinzufügen, falls gelöscht 
 +INSERT INTO users (username, email, display_name) 
 +VALUES('shaolin','shaolin@wetraveltheworld.de','Shaolin Tran'); 
 + 
 +-- 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 = 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 = 1 WHERE post_id = 2;  -- Post #2: Editor = caro
-UPDATE posts SET editor_id = NULL WHERE post_id = 3;  -- Post #3: (noch) kein Editor+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_editor +ADD CONSTRAINT fk_posts_editor 
-    FOREIGN KEY (editor_id) +FOREIGN KEY (editor_id) 
-    REFERENCES users (user_id) +REFERENCES users (user_id) 
-    ON DELETE SET NULL     -- Editor gelöscht → Post bleibt, Verweis wird NULL +ON DELETE SET NULL     -- User gelöscht → Post bleibt, Verweis wird NULL 
-    ON UPDATE RESTRICT;    -- user_id soll nicht geändert werden können (User-PK bleibt stabil; man ändert den Editor immer in posts)+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 Redaktor:in ist **optional**. Wird ein User gelöscht, soll der Post **nicht** mitgelöscht werden – der optionale Verweis fällt einfach 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:** 
-===== 2) N:M-Zuordnung absichern (post_category mit CASCADE) ===== +<WRAP center box 80% round>
-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 ===== 
  
-<WRAP box round center 80%> +===== 2) CASCADE: Kommentare zur posts (comments → posts) ===== 
-**RESTRICT** – schützt Elternzeilen   +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**.
-Eltern (z. B. **users**) können nicht gelöscht/aktualisiert werden, solange Kinder (z. B. **posts**) auf sie verweisen.+
  
-**Demo (soll fehlschlagen):**+**2.1 Tabelle anlegen (mit CASCADE)** 
 +<WRAP box round center 80%>
 <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.1760657679.txt.gz
  • Zuletzt geändert: 2025/10/17 01:34
  • von gkoch