webentwicklung-frage-antwort-db.com.de

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

Ich entwerfe mein Datenbankschema mit MySQL Workbench, was ziemlich cool ist, da Sie Diagramme erstellen und konvertieren können: P

Wie auch immer, ich habe mich für InnoDB entschieden, weil es Fremdschlüssel unterstützt. Eine Sache, die mir aufgefallen ist, ist, dass Sie die Optionen On Update und On Delete für Fremdschlüssel einstellen können. Kann jemand erklären, wo "Restrict", "Cascade" und "set null" in einem einfachen Beispiel verwendet werden könnten?

Angenommen, ich habe eine user -Tabelle, die eine userID enthält. Und sagen wir, ich habe eine Nachrichtentabelle message, die eine Viel-zu-Viel-Tabelle mit 2 Fremdschlüsseln ist (die auf denselben Primärschlüssel userID in der Tabelle user verweisen). . Ist es in diesem Fall sinnvoll, die Optionen "Bei Aktualisierung" und "Bei Löschen" festzulegen? Wenn ja, welches wähle ich? Wenn dies kein gutes Beispiel ist, können Sie bitte ein gutes Beispiel einbringen, um zu veranschaulichen, wie diese nützlich sein können?

Vielen Dank

178
meltuhamy

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.

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

==> 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).

455
regilero

Eine Ergänzung zu @MarkR answer: Es ist zu beachten, dass viele PHP Frameworks mit ORMs das erweiterte DB-Setup (Fremdschlüssel, kaskadiertes Löschen, eindeutige Einschränkungen) nicht erkennen oder verwenden). Dies kann dazu führen in unerwartetem Verhalten.

Wenn Sie beispielsweise einen Datensatz mit ORM löschen und Ihr DELETE CASCADE löscht Datensätze in verknüpften Tabellen. Der Versuch von ORM, diese verknüpften Datensätze (häufig automatisch) zu löschen, führt zu Fehlern.

2
lxa

Sie müssen dies im Kontext der Anwendung berücksichtigen. Im Allgemeinen sollten Sie eine Anwendung und keine Datenbank entwerfen (die Datenbank ist einfach Teil der Anwendung).

Überlegen Sie, wie Ihre Anwendung auf verschiedene Fälle reagieren soll.

Die Standardaktion besteht darin, den Vorgang einzuschränken (d. H. Nicht zuzulassen), was normalerweise gewünscht wird, da dumme Programmierfehler vermieden werden. Ein DELETE CASCADE kann jedoch auch nützlich sein. Es hängt wirklich von Ihrer Anwendung ab und davon, wie Sie bestimmte Objekte löschen möchten.

Persönlich würde ich InnoDB verwenden, weil es Ihre Daten nicht zerstört (vgl. MyISAM, was auch der Fall ist), sondern weil es FK-Einschränkungen hat.

1
MarkR