====== LU08d - n:m-Beziehungen mit einer Zwischentabelle ======

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


===== 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**.

<WRAP center round tip 80%>
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>

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

<WRAP center box 80% round><code sql>
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');
</code></WRAP>

<WRAP center round info 80%>
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.
</WRAP>

{{ :de:modul:m290_guko:learningunits:lu08:theorie:categories.png?direct&400 |Tabelle categories}}


===== Warum ein Fremdschlüssel nicht reicht =====
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:

<WRAP center box 80% round>
^ Versuch ^ Problem ^
| //category_id// in //posts// | Pro Post ist nur **eine** Kategorie möglich |
| //post_id// in //categories// | Pro Kategorie ist nur **ein** Post möglich |
| 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>

{{ :de:modul:m290_guko:learningunits:lu08:theorie:many-to-many_not-resolved.png?direct&800 |Ungeeignete Lösungsversuche für n:m}}


===== Die Zwischentabelle =====
//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.

{{ :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.


===== Zwischentabelle anlegen =====

<WRAP center box 80% round><code sql>
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
);
</code></WRAP>

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.


===== Zuordnungen einfügen =====

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


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

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

----
{{tag>M290-LU08}}
[[https://creativecommons.org/licenses/by-nc-sa/4.0/|{{https://i.creativecommons.org/l/by-nc-sa/4.0/88x31.png}}]] Guido Koch