LU08c - Fremdschlüssel nachträglich hinzufügen und Referenzaktionen

• Ich kann mit ALTER TABLE eine Spalte und einen Fremdschlüssel zu einer bestehenden Tabelle hinzufügen.
• Ich kann die Referenzaktionen RESTRICT, CASCADE und SET NULL erklären.
• Ich kann für eine Beziehung begründen, welche Referenzaktion sinnvoll ist.

In der Praxis entsteht eine Datenbank selten in einem Zug. Oft existieren Tabellen schon mit Daten – und es kommt eine neue Spalte oder eine neue Verknüpfung dazu. Genau so ist es bei Ihren Cases aus LU05: Dort haben Sie die Fremdschlüssel-Spalten angelegt, aber noch nicht als Fremdschlüssel gesetzt. Das holen Sie mit ALTER TABLE nach.

Spalte zu einer bestehenden Tabelle hinzufügen:

ALTER TABLE tabellenname
ADD COLUMN neue_spalte DATENTYP;

Fremdschlüssel nachträglich setzen:

ALTER TABLE kind_tabelle
ADD CONSTRAINT fk_name
FOREIGN KEY (fremdschluessel)
REFERENCES eltern_tabelle (primaerschluessel)
ON DELETE referenzaktion;

fk_name ist ein frei wählbarer Name für die Regel, z.B. fk_posts_editor. So erkennen Sie in Fehlermeldungen sofort, welche Regel gegriffen hat.

Mit ON DELETE legen Sie fest, was passiert, wenn eine Elternzeile gelöscht wird, auf die noch Kindzeilen verweisen:

Die Leitfrage für die Wahl: Ergibt die Kindzeile ohne ihre Elternzeile noch Sinn?

• Nein, sie wäre nutzlos → CASCADE
• Ja, der Verweis ist nur eine Zusatzinformation → SET NULL
• Das Löschen soll gar nicht erst möglich sein → RESTRICT

Jeder Post bekommt neu eine optionale Redaktorin (editor_id), die den Text gegenliest. Wird diese Person gelöscht, soll der Post erhalten bleiben – nur der Verweis fällt weg.

Spalte ergänzen und befüllen:

ALTER TABLE posts
ADD COLUMN editor_id INT NULL;
 
UPDATE posts SET editor_id = 2 WHERE post_id = 1;  -- Hasselt: Martin liest gegen
UPDATE posts SET editor_id = 1 WHERE post_id = 2;  -- Utrecht: Caro liest gegen
UPDATE posts SET editor_id = 3 WHERE post_id = 3;  -- Lissabon: Shaolin liest gegen

Beachten Sie das NULL bei der Spalte: Ohne das wäre SET NULL gar nicht möglich, weil die Spalte keine leeren Werte erlauben würde.

Fremdschlüssel setzen:

ALTER TABLE posts
ADD CONSTRAINT fk_posts_editor
FOREIGN KEY (editor_id)
REFERENCES users (user_id)
ON DELETE SET NULL;

Testen:

DELETE FROM users WHERE username = 'shaolin';
 
SELECT post_id, title, editor_id FROM posts ORDER BY post_id;

Ergebnis: Shaolin ist gelöscht, der Lissabon-Post existiert weiterhin – seine editor_id ist jetzt NULL.

Wir ergänzen eine Tabelle comments. Jeder Kommentar gehört zu genau einem Post. Ein Kommentar ohne Post ist nutzlos – wird der Post gelöscht, sollen seine Kommentare automatisch mit verschwinden.

Tabelle anlegen:

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
);

Hier steht der Fremdschlüssel direkt im CREATE TABLE, wie in LU08b – Referenzaktionen funktionieren auf beiden Wegen.

Befüllen:

INSERT INTO comments (post_id, author, body) VALUES
  (2, 'Leser A',     'Toller Utrecht-Tipp!'),
  (2, 'Leserin B',   'Gute Café-Empfehlungen.'),
  (5, 'Reisefreund', 'Kotor war mein Highlight!');

Testen:

DELETE FROM posts WHERE post_id = 2;
 
SELECT * FROM comments;

Ergebnis: Der Utrecht-Post ist gelöscht – und mit ihm beide Kommentare. Übrig bleibt nur der Kommentar zum Montenegro-Post.

Guido Koch