Fremdschlüsseleinschränkungen: Verwendung von ON UPDATE und ON DELETE

Zögern Sie nicht, Einschränkungen für die Datenbank festzulegen. Sie werden sicher sein, eine konsistente Datenbank zu haben, und das ist einer der guten Gründe, eine Datenbank zu verwenden. Vor allem, wenn Sie mehrere Anwendungen anfordern (oder nur eine Anwendung, aber mit einem Direktmodus und einem Stapelmodus, die unterschiedliche Quellen verwenden).

Mit MySQL haben Sie keine fortgeschrittenen Einschränkungen wie in PostgreSQL, aber zumindest die Fremdschlüssel-Einschränkungen sind ziemlich fortgeschritten.

Wir nehmen ein Beispiel, eine Firmentabelle mit einer Benutzertabelle, die Personen aus dieser Firma enthält

CREATE TABLE COMPANY (
     company_id INT NOT NULL,
     company_name VARCHAR(50),
     PRIMARY KEY (company_id)
) ENGINE=INNODB;

CREATE TABLE USER (
     user_id INT, 
     user_name VARCHAR(50), 
     company_id INT,
     INDEX company_id_idx (company_id),
     FOREIGN KEY (company_id) REFERENCES COMPANY (company_id) ON...
) ENGINE=INNODB;

Schauen wir uns die ON UPDATE -Klausel an:

• ON UPDATE RESTRICT : der Standardwert: Wenn Sie versuchen, eine company_id in der Tabelle COMPANY zu aktualisieren, lehnt die Engine den Vorgang ab wenn ein USER mindestens Links zu dieser Firma hat.
• ON UPDATE NO ACTION : Wie RESTRICT.
• ON UPDATE CASCADE : normalerweise die beste: Wenn Sie eine company_id in einer Zeile der Tabelle COMPANY aktualisieren, wird die Engine Aktualisieren Sie es entsprechend in allen USER-Zeilen, die auf diese FIRMA verweisen (es sind jedoch keine Trigger in der USER-Tabelle aktiviert, Warnung). Der Motor wird die Änderungen für Sie verfolgen, es ist gut.
• ON UPDATE SET NULL : Wenn Sie eine company_id in einer Zeile der Tabelle COMPANY aktualisieren, setzt die Engine die zugehörige USERs company_id auf NULL (sollte in USER company_id verfügbar sein) Feld). Ich kann nichts Interessantes dazu bei einem Update sehen, aber ich kann mich irren.

Und jetzt auf der ON DELETE Seite:

• ON DELETE RESTRICT : der Standardwert: Wenn Sie versuchen, eine company_id-ID in der Tabelle COMPANY zu löschen, weist die Engine die zurück Betrieb, wenn ein Benutzer mindestens Links zu dieser Firma hat, kann Ihr Leben retten.
• ON DELETE NO ACTION : wie RESTRICT
• ON DELETE CASCADE : dangerous: Wenn Sie eine Firmenzeile in der Tabelle COMPANY löschen, löscht die Engine auch die dazugehörige USERs. Dies ist gefährlich, kann aber verwendet werden, um automatische Bereinigungen für Nebentabellen durchzuführen.
• ON DELETE SET NULL : Handvoll: Wenn Sie eine FIRMEN-Zeile löschen, haben die zugehörigen BENUTZER automatisch die Beziehung zu NULL . Wenn Null Ihr Wert für Benutzer ohne Firma ist, kann dies ein gutes Verhalten sein. Möglicherweise müssen Sie die Benutzer in Ihrer Anwendung als Autoren einiger Inhalte behalten, aber das Entfernen der Firma ist für Sie kein Problem.

normalerweise ist mein Standardwert: ON DELETE RESTRICT ON UPDATE CASCADE . mit etwas ON DELETE CASCADE für Track-Tabellen (Protokolle - nicht alle Protokolle-, solche Dinge) und ON DELETE SET NULL wenn die Master-Tabelle ein 'einfaches Attribut' für die Tabelle ist, die den Fremdschlüssel enthält, wie eine JOB-Tabelle für die USER-Tabelle.

Bearbeiten

Es ist lange her, dass ich das geschrieben habe. Jetzt denke ich, ich sollte eine wichtige Warnung hinzufügen. MySQL hat eine große dokumentierte Einschränkung bei Kaskaden. Kaskaden lösen nicht aus . Wenn Sie also zu sicher waren, dass diese Engine Trigger verwendet, sollten Sie Kaskadeneinschränkungen vermeiden.

• http://dev.mysql.com/doc/refman/5.6/en/triggers.html

MySQL-Trigger werden nur für Änderungen aktiviert, die von SQL-Anweisungen an Tabellen vorgenommen wurden. Sie werden weder für Änderungen in Ansichten noch für Änderungen an Tabellen aktiviert, die von APIs vorgenommen wurden, die keine SQL-Anweisungen an den MySQL Server senden

• http://dev.mysql.com/doc/refman/5.6/en/stored-program-restrictions.html#stored-routines-trigger-restrictions

==> Siehe unten, die Dinge bewegen sich auf dieser Domain

Trigger werden nicht durch Fremdschlüsselaktionen aktiviert.

Und ich glaube nicht, dass dies eines Tages behoben wird. Fremdschlüsseleinschränkungen werden vom InnoDb-Speicher und Trigger von der MySQL SQL-Engine verwaltet. Beide sind getrennt. Innodb ist der einzige Speicher mit Constraint-Management. Vielleicht fügen sie eines Tages Trigger direkt in die Speicher-Engine ein, vielleicht auch nicht.

Aber ich habe meine eigene Meinung dazu, welches Element Sie zwischen der schlechten Triggerimplementierung und der sehr nützlichen Unterstützung von Fremdschlüsselbeschränkungen wählen sollten. Und sobald Sie sich an die Datenbankkonsistenz gewöhnt haben, werden Sie PostgreSQL lieben.

12/2017-Aktualisierung dieser Änderung über MySQL:

wie von @IstiaqueAhmed in den Kommentaren angegeben, hat sich die Situation zu diesem Thema geändert. Folgen Sie also dem Link und überprüfen Sie die aktuelle Situation (die sich in Zukunft möglicherweise noch einmal ändern wird).