MySQL Fremdschlüssel

Bei Datenbank wird zwischen Primärschlüsseln und Fremdschlüsseln unterschieden. Ein Primärschlüssel identifiziert jeden Datensatz einer Tabelle eindeutig. Dies ist zumeist eine Id-Spalte eurer Tabelle und beim Erstellen der Tabelle wurde bereits in phpMyAdmin spezifiziert, dass diese Spalte als Primärschlüssel dient.

Fremdschlüssel sind dagegen Spalten die auf Primärschlüssel anderer Tabellen verweisen.

Im obigen Beispiel wären die Spalten id jeweils der Primärschlüssel der Tabellen user und beitraege. Die Spalte userid ist in diesem Beispiel ein Fremdschlüssel der auf die Tabelle users verweist.

Soweit nichts besonders. Ein Problem entsteht nun, falls ihr in der Tabelle users den ersten Eintrag mit der Id 1 löscht. In der Tabelle beitraege habt ihr dann eine tote Verknüpfung. Der erste Beitrag verweist auf den User mit der Id 1, dieser existiert aber nicht mehr. Somit habt ihr Müll in eurer Datenbank, der wiederum später unangenehme Auswirkungen auf eurer Anwendung haben kann.

Fremdschlüssel in MySQL definieren

In MySQL lassen sich, sofern die Speicherengine InnoDB genutzt wird, Fremdschlüsselbeziehungen definieren und überprüfen. Durch die Überprüfung könnt ihr sicherstellen, dass keine toten Verknüpfungen mehr existieren und ihr dadurch Müll in eurer Tabelle besitzt.

In der Übersicht eurer Datenbanktabellen listet euch phpMyAdmin in der Spalte Typ die genutzt Speicherengine auf. Die Speicherengine ist das interne Format, indem MySQL die Daten auf der Festplatte abspeichert. Früher wurde zumeist MyISAM genutzt, seit MySQL 5.5.5 ist allerdings InnoDB die Standard-Speicherengine von MySQL. Persönlich empfehle ich stets InnoDB als Speicherengine zu verwenden, da es mehr Funktionalität als MyISAM anbietet. Die Speicherengine könnt ihr beim Erstellen einer Tabelle in phpMyAdmin auswählen.

Um nun eine Fremdschlüsselbeziehung in phpMyAdmin zu erstellen, müsst ihr folgende Schritte ausführen:

1) Erstellt einen Index für die Fremdschlüsselspalte

Damit ihr einen Fremdschlüssel erstellen könnt, muss diese Spalte als UNIQUE oder als INDEX definiert sein.  Wie dies geht erfahrt ihr im Artikel Optimierung von MySQL: Verwendung des Index.

2) Erstellt eine Fremdschlüsselbedingung

Wechselt dazu in phpMyAdmin in den Struktur-Tab eurer Tabelle in der ihr einen Fremdschüssel erstellen wollt. Im obigen Beispiel also in den Struktur-Tab der beitraege-Tabelle. Dort sollte ein Link Beziehungsansicht oder Beziehungsübersicht zu finden sein. Danach solltet ihr die folgende (oder eine ähnliche) Ansicht erhalten.

Dort könnt ihr auswählen, für welchen Spalte der beitraege-Tabelle ihr einen Fremdschlüssel erstellt wollt. In unserem Beispiel für die Spalte userid. Dieser Fremdschlüssel zeigt dabei auf die Spalte id der Tabelle users (die sich in der Datenbank test befindet). Das Feld für den Constraint-Name könnt ihr leer lassen, phpMyAdmin wählt selbst einen passenden Namen.

Ebenfalls könnt ihr das Verhalten definieren dass bei einem DELETE passieren soll und dass bei einem UPDATE passieren soll.

Angenommen der User wird aus der Users-Tabelle gelöscht. Dann könnt ihr in der Beiträge-Tabelle definieren, was dort mit den Beiträgen des Benutzers passieren soll:

• Bei RESTRICT wird das Löschen des Nutzers verhindert sofern dieser noch Beiträge hat.
• Bei CASCADE werden alle Beiträge des Nutzers gelöscht.
• Bei SET NULL wird die userid für alle Beiträge des Nutzers auf Null gesetzt.
• NO ACTION verhält sich identisch zu RESTRICT. NO ACTION kommt aus dem SQL-Standard, wird aber von MySQL identisch zu RESTRICT behandelt.

Bei ON UPDATE verhält es sich ähnlich. Angenommen die Id des Benutzers verändert sich, so wird bei RESTRICT dieses Update verhindert. Bei CASCADE wird die aktualisierte Nutzer-Id übernommen, sprich, alle Beiträge erhalten damit automatisch die korrekte Id.

Persönlich setze ich den Wert meistens auf RESTRICT oder auf CASCADE. Allerdings solltet ihr bei CASCADE vorsichtig sein. Löscht ihr einen Nutzer versehentlich, dann sind auch sämtliche seiner Beiträge gelöscht. Diese Daten wiederherzustellen kann unangenehm sein.