====== LU08b - Fremdschlüssel beim Erstellen setzen ======

===== Lernziele =====
  * Ich kann erklären, was eine Elterntabelle und was eine Kindtabelle ist.
  * Ich kann mit CREATE TABLE eine Tabelle mit Fremdschlüssel erstellen und weiss, in welcher Reihenfolge die Tabellen entstehen müssen.
  * Ich kann vorhersagen, welche Befehle ein Fremdschlüssel blockiert, und begründen, warum.


===== Eltern- und Kindtabelle =====
Ein Fremdschlüssel verbindet immer zwei Tabellen. Diese zwei Tabellen haben eine feste Rolle:

<WRAP center box 80% round>
^ Rolle ^ Was sie ausmacht ^ Im Reiseblog ^
| **Elterntabelle** | Auf sie wird verwiesen. Sie enthält den Primärschlüssel, auf den der Fremdschlüssel zeigt. | //users// |
| **Kindtabelle** | Sie enthält den Fremdschlüssel. Jede Zeile darin verweist auf eine Zeile der Elterntabelle. | //posts// |
</WRAP>

Als Eselsbrücke hilft das Bild einer Familie: Ein Kind hat Eltern, auf die es sich bezieht. Ein Post hat eine Autorin. Umgekehrt kann eine Autorin viele Posts haben, so wie Eltern mehrere Kinder haben können. Die Kindtabelle ist also immer die "viele"-Seite.

{{ :de:modul:m290_guko:learningunits:lu08:theorie:crowsfoot_wordpress_reiseblog.drawio.png?nolink&1100 |ERD: users zu posts (1:n) und posts zu categories (n:m)}}

Auf dieser Seite verbinden wir //users// und //posts//. Die Kategorien kommen später dazu.

<WRAP center round info 80%>
Die Tabellen heissen hier im **Plural** (//users//, //posts//), weil WordPress sie so benennt (//wp_users//, //wp_posts//). In Ihren eigenen Cases bleiben wir beim Singular.
</WRAP>


===== Die Grundsyntax =====

<WRAP center box 80% round><code sql>
CREATE TABLE kind_tabelle (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  fremdschluessel INT NOT NULL,
  FOREIGN KEY (fremdschluessel) REFERENCES eltern_tabelle (primaerschluessel)
);
</code></WRAP>

Zwei Regeln gilt es zu beachten:

  * **Die Elterntabelle muss zuerst existieren.** Ein Fremdschlüssel kann nur auf eine Tabelle verweisen, die es schon gibt. Deshalb entsteht zuerst //users//, danach //posts//.
  * **Der Datentyp muss passen.** Ist der Primärschlüssel der Elterntabelle ein INT, muss auch die Fremdschlüssel-Spalte ein INT sein.


===== Das Beispiel =====

**Elterntabelle ''users'':**
<WRAP center box 80% round><code sql>
CREATE TABLE users (
  user_id       INT AUTO_INCREMENT PRIMARY KEY,
  username      VARCHAR(60)  NOT NULL UNIQUE,
  email         VARCHAR(100) NOT NULL UNIQUE,
  display_name  VARCHAR(100) NOT NULL,
);
</code></WRAP>

**Kindtabelle ''posts'':**
<WRAP center box 80% round><code sql>
CREATE TABLE posts (
  post_id      INT AUTO_INCREMENT PRIMARY KEY,
  title        VARCHAR(200) NOT NULL,
  featured_img VARCHAR(512),
  content      TEXT,
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  author_id    INT          NOT NULL,
  FOREIGN KEY (author_id) REFERENCES users (user_id)
);
</code></WRAP>

Neu ist die letzte Zeile. //posts.author_id// verweist auf //users.user_id//. Ab jetzt prüft MySQL bei jedem Post, ob die angegebene Autorin wirklich existiert.

Im Video sehen Sie, wie die beiden Tabellen entstehen, mit Daten gefüllt werden und wie sich der Fremdschlüssel verhält:

{{url>PLATZHALTER-VIDEO-FK-CREATE-TABLE 1200,675 noscroll noborder center|Screencast: Tabellen mit Fremdschlüssel erstellen und testen}}
//Platzhalter für das Screencast-Video.//

Nach dem Einfügen sieht die Verknüpfung so aus. Caro hat die //user_id// 1, Martin die 2 und Shaolin die 3. Shaolin hat noch keinen Post.

{{ :de:modul:m290_guko:learningunits:lu08:theorie:reiseblog-connected_users-posts.png?direct&1600 |Verknüpfung von users und posts}}


===== Was der Fremdschlüssel bewirkt =====
Der Fremdschlüssel verhindert **verwaiste Daten**. Es kann keinen Post geben, dessen Autorin nicht existiert. MySQL achtet dabei auf beide Tabellen:

  * In der **Kindtabelle** muss jeder neue Post auf einen existierenden User verweisen.
  * In der **Elterntabelle** darf ein User nicht gelöscht werden, solange noch Posts auf ihn verweisen. Auch seine //user_id// darf dann nicht geändert werden.

Dieses Verhalten heisst **RESTRICT**. Es gilt automatisch, wenn nichts anderes angegeben ist.

Die folgenden Befehle zeigt auch das Video. Überlegen Sie bei jeder Zeile zuerst selbst, ob der Befehl funktioniert, bevor Sie das Ergebnis lesen.

<WRAP center box 80% round>
^ Befehl ^ Ergebnis ^ Warum ^
| ''INSERT INTO posts (title, author_id) VALUES ('Testpost', 99);'' | blockiert | Einen User mit der ID 99 gibt es nicht |
| ''DELETE FROM users WHERE username = 'shaolin';'' | erlaubt | Kein Post verweist auf Shaolin |
| ''DELETE FROM users WHERE username = 'martin';'' | blockiert | Drei Posts verweisen auf Martin. Sie hätten danach keine gültige Autorin mehr. |
| ''UPDATE users SET username = 'martin_travels' WHERE user_id = 2;'' | erlaubt | Der Fremdschlüssel zeigt auf //user_id//, nicht auf //username// |
| ''UPDATE users SET user_id = 5 WHERE user_id = 2;'' | blockiert | Die Posts mit //author_id// 2 würden ins Leere zeigen |
</WRAP>

Bei einem blockierten Befehl meldet MySQL einen Fehler, zum Beispiel beim Löschen von Martin:

<WRAP center round alert 80%>
[23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (travel_blog.posts, CONSTRAINT posts_ibfk_1 FOREIGN KEY (author_id) REFERENCES users (user_id))
</WRAP>

Die Meldung nennt die Kindtabelle (//posts//), die Spalte (//author_id//) und die Elterntabelle (//users//). So lässt sich nachvollziehen, welche Verknüpfung den Befehl verhindert hat. Diese Meldung werden Sie bei der Übung mit Ihrem eigenen Case wiedersehen.

<WRAP center round box 80%>
{{youtube>QDmJBSgnUEo?}}\\
//SQL Schlüssel einfach erklärt (7:49 min, deutsch). Quelle: Informatik, simpleclub / YouTube//
</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