8. Tabellen verbinden

Zur Erinnerung, aktuell haben wir folgende zwei Tabellen:

Und die Struktur von "tblkunden" sieht wie folgt aus:

Was bereits gut ist (und niemals fehlen sollte), ist eine ID, die automatisch hochzählt und als Primärschlüssel definiert ist. Das haben wir mit dem Attribut "tKundenID" gemacht.

Ebenso in der Tabelle "tbllaender" haben wir einen Primärschlüssel: "tLaenderID".

Das Verknüpfen von Tabellen hat einen einfachen Grund. Schauen Sie dazu gleich mal unsere Kund*innen an:

In der letzten Spalte sehen Sie das Attribut "tLandID".

Aktuell können Sie dort reinschreiben, was Sie wollen. Ein NULL-Wert haben wir zwar nicht erlaubt.

Aber wir können da die Zahl 0 reinschreiben. Oder wir können die Zahl 250 reinschreiben.

Nun, es geht, wie wir das bereits im Einführungsmodul zu Datenbanken gesehen haben, darum, dass wir konsistente Daten haben wollen. Es macht einfach keinen Sinn, dass wir bei einer Kundin das Land 250 drin haben, wenn unsere Tabelle nur bis 239 geht:

Und es macht auch keinen Sinn, wenn ein Kunde das Land 0 drin hat, wenn die Länderliste bei 1 beginnt:

Umgekehrt sind Sie aktuell in der Lage, in der Tabelle "tbllaender" den Datensatz 43 zu löschen (Deutschland). Das führt aber wieder zu inkonsistenten Daten: unser Stefan Derrick hat dann ein Land eingetragen, das nicht mehr existiert. Das ist in einer Datenbank nicht erwünscht.

Dadurch, dass wir in MySQL (und natürlich in anderen RDBMS) einen Fremdschlüssel definieren, stellen wir eine Verbindung zwischen den beiden Tabellen her. Und das führt primär dazu, dass die beiden Zahlenwerte übereinstimmen müssen.

UPDATE tblkunden SET tLandID = 250 WHERE tKundenID = 1;

So, und nun definieren wir das Attribut "tLandID" in der Tabelle "tblkunden" als Fremdschlüssel. Das geht wie folgt:

ALTER TABLE tblKunden
ADD FOREIGN KEY (tLandID) REFERENCES tbllaender (tLandID);

Und da kommt nun eine wunderschöne Fehlermeldung:

Also ändern wir die tLandID von Stefan Derrick wieder zurück auf 43.

Führen wir nun obigen Befehl nochmals aus, um den Fremdschlüssel zu definieren, scheint das jetzt zu funktionieren:

Schauen wir auch gleich die Tabellenstruktur an:

Nun steht in der Spalte "Key" der Wert "MUL". Das ist korrekt. Das soll zeigen, dass mehrmals der gleiche Wert vorkommen kann. Und das ist natürlich der Fall. Wir haben ja mehrere Kund*innen aus der Schweiz. Da darf überall der Wert 182 drin vorkommen. Sonst wäre es ziemlich sinnlos.

PERFEKT😎. Wir haben erreicht, was wir wollen! Wir können in der Kundentabelle keine ungültigen Werte als Land eingeben.

Das hat funktioniert, nicht wahr?

Es erscheint wieder eine gefährliche Warnmeldung:

Die will nichts anderes sagen, als dass wir nicht ein Land löschen können, das in der Tabelle "tblkunden" verwendet wird. Tönt irgendwie logisch oder?

Das ist das Standardverhalten, welches aber angepasst werden kann.

Es gibt folgende Aktionen auf der 1-Seite einer Beziehung, welche die n-Seite betreffen kann:

• Löschen eines Datensatzes
• Ändern eines Primärschlüssels

Mit Löschen eines Datensatzes ist gemeint, dass wir in unserem Beispiel ein Land aus der Länder-Tabelle entfernen. Das kann durchaus vorkommen in der Praxis.

Mit Ändern des Primärschlüssels ist gemeint, dass wir beispielsweise die Land-ID 43 bei Deutschland ändern auf den Wert 250. Das ist in unserem Fall gar nicht möglich, weil wir ja einen Autoincrement haben. Diese Nummer können wir gar nicht festlegen.

Entfernen Sie testeshalber das Autoincrement in der Tabelle "tbllaender". Das geht wie folgt:

ALTER TABLE tbllaender MODIFY tLandID TINYINT UNSIGNED;

Die Information bezüglich Autoincrement fehlt nun:

Das erlaubt uns, nachher rasch zu testen, ob wir den Primärschlüssel ändern können resp. wir können anschauen, wie man hier das Verhalten anpassen kann.

Schauen wir aber zuerst, was beim Löschen passieren kann.

Das Verhalten kann verändert werden. Nachträglich ist das aber ein Murks. Drum besser gleich bei Erstellen des Fremdschlüssels dran denken.

Das ist grundsätzlich der Befehl, um einen Constraint bezüglich Löschen auf dem Fremdschlüssel "tLandID" zu setzen:

ALTER TABLE tblKunden ADD CONSTRAINT FOREIGN KEY (tLandID) REFERENCES tbllaender (tLandID) ON DELETE CASCADE;

Das Dumme ist aber, wenn wir das ausführen, dann wird das erledigt. Aber nicht so, wie wir uns das wünschen…

Wenn wir alle Fremdschlüssel anzeigen wollen, dann sollten wir ja folgendes kriegen:

Wir kriegen nach dem obigen ALTER TABLE aber das:

MySQL ändert also nicht den bestehenden Fremdschlüssel, sondern füg einfach einen zweiten an. Das ist grundsätzlich nicht tragisch, aber dann müssen wir den ersten halt löschen:

ALTER TABLE tblkunden DROP FOREIGN KEY tblkunden_ibfk_1;

Wie zum Geier kriegt man den Namen der Fremdschlüssel? Hehe, das ist ein Geheimnis😅. Nein, einfach den folgenden SQL-Befehl ausführen:

SELECT RefCons.constraint_schema, RefCons.table_name, RefCons.referenced_table_name, RefCons.constraint_name, KeyCol.column_name
FROM information_schema.referential_constraints RefCons
JOIN information_schema.key_column_usage KeyCol ON RefCons.constraint_schema = KeyCol.table_schema
     AND RefCons.table_name = KeyCol.table_name
     AND RefCons.constraint_name = KeyCol.constraint_name
WHERE RefCons.constraint_schema = 'kundendb';

Der zeigt sämtliche Fremdschlüssel der ganzen Datenbank, schön aufgelistet in einer Tabelle:

Es gibt noch einen kürzeren Befehl:

SHOW CREATE TABLE kundendb.tblkunden;

Da kommt als Ausgabe noch viel mehr (zudem ein paar Bindestriche zu viel😂):

Da sehen Sie auch sämtliche Attribute plus ein paar andere Informationen bezüglich Database-Engine und Zeichensatz. Uns interessiert in diesem Fall aber nur die zweitletzte Zeile mit dem CONSTRAINT.

Weiter oben haben Sie ja das Verhalten bei Schlüsselverletzung angepasst:

ON DELETE CASCADE

Nun können Sie mal versuchen, Deutschland zu löschen:

DELETE FROM tbllaender WHERE tLandID = 43;

Wenn alles geklappt hat, sollte folgende Meldung erscheinen:

Was ist nun passiert? Listen Sie doch mal alle Kund*innen auf…

Bei mir sieht das Ergebnis so aus:

Mist, wo ist denn Stefan Derrick hin? Und Datensatz Nr. 1 fehlt bei mir auch. Was ist falsch gelaufen?

In der Theorie gar nichts…mit dem Constraint "ON DELETE CASCADE" sagen wir der Datenbank, dass sie, sobald wir ein Land löschen, auch sämtliche Datensätze auf der n-Seite der Beziehung löschen sollen, welche davon betroffen sind.

Konkret heisst das, ich habe alle deutschen Kunden gelöscht…shit😢.

Sie haben gesehen:

ON DELETE CASCADE

ist echt nicht nett…das löscht uns ungefragt Kunden.

Es gibt bessere Varianten:

• ON DELETE SET DEFAULT
  falls ein Standardwert festgelegt wurde, z.B. 182 für Schweiz
• ON DELETE SET NULL
  falls das Attribut NULL-Werte zulässt
• ON DELETE NO ACTION
  beste Variante, man kann kein Land löschen, wenn noch Kund*innen vorhanden sind mit diesem Land

ALTER TABLE tblkunden MODIFY COLUMN tLandID TINYINT UNSIGNED NULL;

ALTER TABLE tblKunden ADD CONSTRAINT FOREIGN KEY (tLandID) REFERENCES tbllaender (tLandID) ON DELETE SET NULL;

So, hier als Vorher-Nachher-Vergleich die Tabelle "tblkunden":

Vor dem Löschen der Schweiz:

Nach dem Löschen der Schweiz:

Immerhin haben wir nun unsere Schweizer Kund*innen nicht gelöscht…das lässt sich noch retten.

Versuchen wir das auch noch gleich.

INSERT INTO tbllaender (tLandID, tKurz, tLand) VALUES (182,"CH","Schweiz");

ALTER TABLE tblkunden MODIFY COLUMN tLandID TINYINT UNSIGNED NULL DEFAULT 182;

Und jetzt sollte der Default-Wert ersichtlich sein:

UPDATE tblkunden SET tLandID = 200 WHERE tLandID IS NULL;

Das sollte uns die folgende Kundenliste liefern:

Jetzt kommt halt wieder ein wenig die mühsame Arbeit:

Jetzt kommt der grosse Moment. Was passiert, wenn wir das Land 200 löschen?

Wir haben vorhin das Autoincrement von der Tabelle "tbllaender" entfernt. Primärschlüssel ist das Feld "tLandID" aber immer noch.

Also können Sie testen, was passiert, wenn Sie eine tLandID in der Tabelle "tbllaender" ändern, z.B. die 182 auf 240.

Sie müssen vorgängig den Constraint anpassen auf ON UPDATE SET NULL.

Achtung, das macht wenig Sinn🙈..

Die einzigen beiden Constraints hier, die Sinn machen, sind:

• ON UPDATE NO ACTION
• ON UPDATE CASCADE

• ON DELETE
  • NO ACTION
  • CASCADE (Vooooooorsicht!)
  • SET DEFAULT (z.B. 182 für Schweiz)
  • SET NULL (geht nur, wenn tLandID NULL-Werte zulässt)
• ON UPDATE
  • NO ACTION (bei Autoincrement sinnlos)
  • CASCADE (bei Autoincrement sinnlos, sonst sinnvoll)
  • SET DEFAULT (Blödsinn)
  • SET NULL (Blödsinn)

Wir haben in unserem Beispiel den Fremdschlüssel "tLandID" erst nachträglich erstellt.

In der Regel macht es Sinn, dies gleich von Anfang an zu erledigen, sprich, beim Erstellen der Tabelle.

Der Befehl CREATE TABLE muss lediglich durch die Zeile mit dem FOREIGN KEY ergänzt werden:

CREATE TABLE tblkunden
(
tKundenID SMALLINT AUTO_INCREMENT,
tVorname VARCHAR(40) NOT NULL,
tNachname VARCHAR(40) NOT NULL,
tStrasse VARCHAR(80),
tPLZ MEDIUMINT,
tOrt VARCHAR(60),
tLandID TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (tKundenID),
FOREIGN KEY (tLandID) REFERENCES tbllaender (tLandID) ON DELETE SET NULL ON UPDATE CASCADE
);

Beachten Sie dementsprechend, dass Sie in einem Datenmodell immer zuerst alle Tabellen erstellen, die auf der "1-Seite" einer Beziehung stehen. Also eben in diesem Fall zuerst die Tabelle "tbllaender" und dann erst die Tabelle "tblkunden":