LU08b - Fremdschlüssel beim Erstellen setzen

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

Ein Fremdschlüssel verbindet immer zwei Tabellen. Diese zwei Tabellen haben eine feste Rolle:

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.

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

CREATE TABLE kind_tabelle (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  fremdschluessel INT NOT NULL,
  FOREIGN KEY (fremdschluessel) REFERENCES eltern_tabelle (primaerschluessel)
);

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.

Elterntabelle users:

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

Kindtabelle posts:

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

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:

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.

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.

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

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.

Guido Koch