Ein Blick in die Roadmap von MySQL verrät: Ab MySQL 6 soll die referentielle Integrität in allen Storage Engines gewährleistet werden können. Wer nicht solange wartet kann oder will, hat ab MySQL 5.0 die Chance, diese Funktionalität über Trigger nachzubilden.
Referentielle Integrität garantiert, dass in einer Gruppe von Tabellen, die semantisch miteinander in Beziehung stehen, keine verwaisten Einträge entstehen. Am konkreten Beispiel bedeutet das:
Wir haben eine Tabelle User, in der wir Benutzerdaten einer Blog-Community speichern. Darin enthalten sind Benutzername, Email-Adresse, Datum des Beitritts zur Community usw. Eine weitere Tabelle nimmt die Blogeinträge der User auf, also deren Überschrift, den Text und den Zeitpunkt des Eintrags. Gemäß der Normalisierung existiert in der Tabelle der Blogeinträge nur ein Verweis zur ID des Users (im Attribut autor), Abfragen wie die folgende müssen Userdaten somit via Join aus beiden Tabellen holen.
SELECT u.name, b.titel, b.text
FROM user u, blogbeitrag b
WHERE b.autor=u.uid AND b.bid = "123"
Beendet nun ein Benutzer seine Mitgliedschaft in der Community und wird der Datensatz aus der Tabelle Users gelöscht, erscheint für seine Blogeinträge kein Autorenname mehr. Blogeinträge werden zu so genannten Waisen. In der Praxis lassen sich solch unangenehmen Situation vermeiden, indem man nullwertige Referenzen filtert.
Abhilfe in diesem oben genannten Fall schafft ein Fremdschlüssel, der die referentielle Integrität gewährleistet. Das Attribut u.uid aus obigen Beispiel, also der Primärschlüssel der Usertabelle – hier die so genannte Elterntabelle-, wird in der Blogeintragstabelle – in diesem Fall als Kindtabelle bezeichnet – im Attribut b.autor referenziert. In MySQL ist das bis zur Version 5.1 nur in der InnoDB Storage Engine möglich.
Trigger
Um referentielle Integrität für eine MyISAM Tabelle in MySQL nachzubilden, benötigt man vier Trigger, jeweils zwei für die Eltern- und Kindtabelle. Die Trigger überprüfen jede Datenbankoperation, für die sie definiert sind – also INSERT-, UPDATE- und DELETE-Anweisungen -, vor ihrer Ausführung. Als Prüfung ist eine wechselseitige Abfrage nötig, das heißt die Trigger der Kindtabelle führen ein SELECT auf die Elterntabelle aus und umgekehrt. Gesucht wird die Anzahl der semantisch abhängigen Datensätze. Kommt der Trigger zu dem Ergebnis, dass Integritätsbedingungen durch die anstehende Operation verletzt werden, wird ein Fehler erzeugt. Da MySQL derzeit noch über kein entsprechendes Gegenstück zu dem SIGNAL aus Oracle oder ähnlichen Mechanismen anderer Datenbanksysteme verfügt, mit dem der Benutzer einen Systemfehler hervorrufen kann, muss man hierbei zu einem kleinen Trick greifen:
Durch eine fehlerhafte SQL-Anweisung verursacht man einen MySQL-Fehler, der nicht nur die Ausführung des Triggers beendet, sondern auch die ursprüngliche Datenbankoperation verhindert. Schöpfen kann man dabei aus dem reichhaltigen Fundus der MySQL-Server-Fehler, die im Anhang B des Handbuchs aufgelistet sind. Welchen Fehler man wählt, ist egal; nur muss man im Nachhinein noch wissen, dass man eben diesen Fehler, der einem beim Debugging begegnet, zweckentfremdet wurde.
Trigger nutzen wie Stored Procedures das Semikolon als Zeilenendzeichen, das standardmäßig auch alle anderen Datenbankoperationen beendet. Damit es zu keiner Fehlermeldung bei der Triggererstellung kommt, ersetzen wir den Standard durch eine doppelte Raute (##):
DELIMITER ##
Danach erzeugen wird zuerst die Trigger für die Kindtabelle:
CREATE TRIGGER bi_blogeintrag BEFORE INSERT ON blogeintrag FOR EACH ROW
BEGIN
DECLARE cnt INT;
SELECT COUNT(uid) INTO cnt FROM user WHERE uid=NEW.autor;
IF cnt=0 THEN
SELECT * INTO cnt FROM DUAL;
END IF;
END ##
Das bi_* im Triggernamen steht für BEFORE INSERT und dient nur der schnellen Erkennung der Funktionalität. Der Name spielt für die Funktion keine Rolle und kann frei gewählt werden. Die Anzahl der abhängigen Datensätze speichern wir in cnt, um es gleich danach wieder abzufragen. Wurden keine Datensätze gefunden, erzeugen wir mit dem zweiten SELECT den Fehler #1096 – No table used. Der INSERT-Trigger stellt dadurch sicher, dass jeder Eintrag in der Kindtabelle einen korrespondierenden Eintrag in der Elterntabelle hat. Neben dem INSERT-Trigger brauchen wir für die Kindtabelle noch einen Trigger, der Updates überprüft – für den Fall, dass der Autor eines Eintrags verändert werden soll:
CREATE TRIGGER bu_blogeintrag BEFORE UPDATE ON blogeintrag FOR EACH ROW
BEGIN
DECLARE cnt INT;
SELECT COUNT(uid) INTO cnt FROM user WHERE uid=NEW.autor;
IF cnt=0 THEN
SELECT * INTO cnt FROM DUAL;
END IF;
END ##
Für die Elterntabelle müssen andersartige Trigger definiert werden: jeweils einer für die Operationen UPDATE und einer für DELETE. Der Aufbau ist analog zu den Triggern der Kindtabelle.
CREATE TRIGGER bu_parent BEFORE UPDATE ON parent FOR EACH ROW
BEGIN
DECLARE cnt INT;
SELECT COUNT(bid) INTO cnt FROM blogeintrag WHERE b=OLD.uid;
IF cnt>0 THEN
SELECT * INTO cnt FROM DUAL;
END IF;
END ##
Last but not least:
CREATE TRIGGER bd_parent BEFORE DELETE ON parent FOR EACH ROW
BEGIN
DECLARE cnt INT;
SELECT COUNT(bid) INTO cnt FROM blogeintrag WHERE bid=OLD.uid;
IF cnt>0 THEN
SELECT * INTO cnt FROM DUAL;
END IF;
END ##
Fazit
Die Triggerlösung ist eine gangbare Alternative zu Fremdschlüsseln für die Einhaltung referentieller Integrität, solange MySQL 5.2 noch nicht veröffentlicht ist. Nichtsdestotrotz ist dies nur eine Übergangslösung, da das Nachprogrammieren von Kernfuktionalitäten selten an die Performance der eigentlichen Lösung heranreicht. Wie groß der Leistungsunterschied wirklich ist, werden jedoch erst spätere Tests zeigen.
Gefällt mir Wird geladen …
Eine detailliertere Übersicht findet sich hier.
Stefan Reimers' Blog 
Du muss angemeldet sein, um einen Kommentar zu veröffentlichen.