LU09b - n:m-Beziehungen mit einer Zwischentabelle

• 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.

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.

Zuerst braucht der Reiseblog eine Tabelle für die Kategorien:

CREATE TABLE categories (
  category_id INT AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  slug        VARCHAR(120) NOT NULL UNIQUE
);
 
INSERT INTO categories (name, slug) VALUES
  ('Städtereise', 'staedtereise'), ('Abenteuer', 'abenteuer'),
  ('Roadtrip', 'roadtrip'),        ('Belgien', 'belgien'),
  ('Niederlande', 'niederlande'),  ('Portugal', 'portugal'),
  ('Deutschland', 'deutschland'),  ('Montenegro', 'montenegro'),
  ('Oman', 'oman'),                ('USA', 'usa');

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:

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.

Gehört der Utrecht-Post zu zwei Kategorien, gibt es dafür zwei Zeilen. In jeder Zelle steht so wieder genau ein Wert.

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.

CREATE TABLE post_category (
  post_id     INT NOT NULL,
  category_id INT NOT NULL,
  PRIMARY KEY (post_id, category_id),
  FOREIGN KEY (post_id) REFERENCES posts (post_id)
    ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES categories (category_id)
    ON DELETE CASCADE
);

Zwei Dinge sind neu.

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.

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.

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

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:

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.

Guido Koch