Deferred Foreign Key Constraints in PostgreSQL verstehen – Bruno Scheufler (2023)

Möglicherweise haben Sie ein Szenario erlebt, in dem eine herkömmliche Fremdschlüsseleinschränkung verletzt wird, beispielsweise wenn Zirkelverweise erstellt werden oder wenn mehrere Entitäten von einer Ressource abhängen und alle Zeilen auf einmal gelöscht werden. Manchmal sollten Zeilen, die auf gelöschte oder aktualisierte Entitäten verweisen, bestehen bleiben, manchmal sollte der Löschvorgang kaskadiert werden und auch alle referenzierenden Zeilen umfassen.

PostgreSQL bietet eine breite Palette von Optionen für Fremdschlüssel, einschließlich der Markierung von Fremdschlüsseleinschränkungen als aufschiebbar in komplexen Fällen. In diesem Leitfaden gehen wir alle Optionen durch, die Ihnen beim Konfigurieren von Fremdschlüsseleinschränkungen zur Verfügung stehen, wie Sie den Modus erkennen können, auf den eine vorhandene Fremdschlüsseleinschränkung eingestellt ist, und wie Sie Ihre Daten anhand dieser Konzepte modellieren.

Im weiteren Verlauf des Leitfadens werden wir die meisten Teile der Fremdschlüssel-Erstellungsgrammatik durchgehen, daher kann es hilfreich sein, Folgendes im Hinterkopf zu behalten

VERWEISEnachrüstbar[ (refcolumn) ] [ ÜBEREINSTIMMEN VOLL | ÜBEREINSTIMMEN TEILWEISE | ÜBEREINSTIMMEN EINFACH ] [ AN LÖSCHENreferential_action] [ AN AKTUALISIERENreferential_action]}[AUFSCHIEBBAR| NICHTAUFSCHIEBBAR] [ZUERST VERSCHOBEN|ZUERST SOFORT]

Eine kurze Zusammenfassung zu Fremdschlüsseln

Fremdschlüsseleinschränkungen sind die Standardmaßnahme zur Gewährleistung der referenziellen Integrität in relationalen Datenbanken. Wenn das etwas zu akademisch klang, lassen Sie uns jeden Teil dieser Aussage aufschlüsseln.

Einschränkungen sind, wie der Name schon sagt, Möglichkeiten, die Daten in Ihrer Datenbank so einzuschränken, dass sie Ihren Erwartungen entsprechen. Zusätzlich zu den Fremdschlüsseln gibt es Primärschlüssel, die eine bestimmte Zeile eindeutig identifizieren, eindeutige Einschränkungen, die Eindeutigkeit gewährleisten, Nicht-Null-Schlüssel und Prüfeinschränkungen.

Fremdschlüssel sind hilfreich, wenn SieReferenzDaten, die in einer anderen Tabelle oder Zeile leben. Normalerweise werden Fremdschlüssel verwendet, wenn Sie Ihr Datenmodell so normalisieren, dass es in mehreren Tabellen lebt.

Betrachten wir als Beispiel den Fall der ModellierungPostUndAutor, die in verschiedenen Tabellen leben, wo diePostTisch hat eineAutorSpalte, die auf die verweistAutordurch seinen Primärschlüssel.

ERSTELLEN TISCH "Autor" ("Ausweis" Varchar(64) NICHT NULL PRIMÄR SCHLÜSSEL,"Name" Varchar(256) NICHT NULL);ERSTELLEN TISCH "Post" ("Ausweis" Varchar(64) NICHT NULL PRIMÄR SCHLÜSSEL,"Autor" Varchar(64) NICHT NULL,"Titel" Varchar(512) NICHT NULL,"Inhalt" Text NICHT NULL,ZWANG „post_author_fkey“ AUSLÄNDISCH SCHLÜSSEL ("Autor") VERWEISE "Autor" ("Ausweis"));

In diesem Beispiel ist dieAutorSpalte hat eine Nicht-Null-Einschränkung, um sicherzustellen, dass es immer eine gibtAutorverbunden mit derPost. Die letzte Einschränkung gibt einen Fremdschlüssel an, der dann jeden Wert im sicherstelltAutorDie Spalte verweist auf eine vorhandene Zeile, die durch identifiziert wirdAusweisSpalte vonAutor. Beim Definieren der Fremdschlüsseleinschränkung können Sie dies tunnurReferenzspalten, die sindPrimärschlüsseloder bilden Sie eineeinzigartige Beschränkungauf der Referenztabelle.

Nun, das ist großartig, aber was ist, wenn Sie einen Autor löschen? Sollten wir auch ihre Beiträge löschen? Oder sollten wir einen Fehler auslösen, sodass die Anwendung alle mit einem Autor verknüpften Beiträge löschen muss, bevor der Autor gelöscht werden kann? Diese Entscheidungen können mit modelliert werdenreferenzielle Aktionen.

Referenzielle Aktionen (bei Aktualisierung, bei Löschung)

Referenzielle Aktionen geben das erwartete Verhalten an, wenn eine referenzierte Zeile gelöscht wird (AUF LÖSCHEN) oder Daten in den referenzierten Spalten werden aktualisiert (AUF UPDATE).

Zu den verfügbaren Aktionen gehören:

• nichts tun (KEINE AKTIONoderBESCHRÄNKEN, die feine Unterschiede aufweisen, die später erläutert werden) und das Nichtbestehen der Aussage
• kaskadierende Zeilen, die auf die gelöschte Zeile verweisen, oder Aktualisierungsspalten, die auf die aktualisierte Spalte verweisen (KASKADE)
• Setzen der Referenzierungsspalte auf null (NULL SETZEN)
• Festlegen der Referenzspalte auf ihre Standardwerte (STANDARD EINSTELLEN)

Sie deklarieren diese Aktionen, wenn Sie die Einschränkung hinzufügen

ERSTELLEN TISCH "Post" (...ZWANG „post_author_fkey“AUSLÄNDISCH SCHLÜSSEL ("Autor")VERWEISE "Autor" ("Ausweis")AN LÖSCHEN KASKADE);

Prüfungen von Zeiteinschränkungen

Wann überprüft PostgreSQL, ob referenzierte Zeilen vorhanden sind? Die Antwort lautet wie so oft: Es kommt darauf an. Standardmäßig überprüft PostgreSQL die referenzielle Integrität nach jeder Anweisung.

Dies kann jedoch geändert werden, indem Sie die verwendenEINSCHRÄNKUNGEN FESTLEGENBefehl, der es ermöglicht, die Einschränkungsprüfung für die auf „verzögert“ oder „sofort“ festzulegenaktuelle Transaktion.

Aufgeschobene Einschränkungenwerden überprüftam Ende der Transaktion, während unmittelbare Einschränkungen nach jeder Anweisung überprüft werden.

SATZEINSCHRÄNKUNGENALLEVERSCHOBEN;SATZEINSCHRÄNKUNGEN„post_author_fkey“VERSCHOBEN;

Aber es gibt einen Haken: Das Aufschieben von Einschränkungen funktioniert nur, wenn die Fremdschlüsseleinschränkungen als aufschiebbar markiert wurden.

Im vorherigen Abschnitt über referenzielle Aktionen habe ich gesagt, dass wir uns mit den Unterschieden befassen werdenKEINE AKTIONUndBESCHRÄNKENausführlicher:BESCHRÄNKENist die stärkste Maßnahme, um zu verhindern, dass referenzierte Zeilen gelöscht werden, da die Prüfung nicht verschoben werden kann.KEINE AKTIONlöst auch einen Fehler aus, wenn der referenzierte Wert nicht vorhanden ist, kann aber zurückgestellt werden, was Ihnen etwas mehr Freiheit beim Löschen von Daten gibt.

Aufschiebbare Einschränkungen

Beim Erstellen einer Einschränkung ist das StandardverhaltenNICHT AUFSCHIEBBARDies bedeutet, dass Sie Einschränkungsprüfungen nicht bis zum Ende der Transaktion verschieben können. Sie können jedoch angeben, dass eine Einschränkung gelten sollAUFSCHIEBBAR. In diesem Fall können Einschränkungen verschoben werden, die Einschränkungsprüfungen werden jedoch standardmäßig durchgeführtSOFORT, also nach jeder Aussage. Dies kann mit eingestellt werdenZunächst aufschiebbar.

ERSTELLEN TISCH "Post" (...ZWANG „post_author_fkey“AUSLÄNDISCH SCHLÜSSEL ("Autor")VERWEISE "Autor" ("Ausweis")AN LÖSCHEN KASKADEAUFSCHIEBBAR ANFANGSVERSCHIEBEN);

Sie können die Aufschiebbarkeit von Einschränkungen auch später ändern

ÄNDERN TISCH [ WENN EXISTIERT ] [NUR]Name[ * ]ÄNDERN ZWANGEinschränkungsname[AUFSCHIEBBAR| NICHTAUFSCHIEBBAR] [ZUERST VERSCHOBEN|ZUERST SOFORT]

Wenn wir unseren Beispielfremdschlüssel nach der Erstellung der ersten, nicht aufschiebbaren Version ändern wollten, würden wir Folgendes ausführen:

ÄNDERN TISCH "Post" ÄNDERN ZWANG „post_author_fkey“AUFSCHIEBBAR;

In den meisten Fällen sind nicht aufschiebbare Einschränkungen völlig in Ordnung, und Sie müssen die Einschränkungsprüfungen möglicherweise nie aufschieben. Aber manchmal stoßen Sie auf Probleme mit Verletzungen der Fremdschlüsselintegrität, sodass Sie jetzt über alle Tools verfügen (Einschränkung als aufschiebbar markieren, Einschränkungen aufschieben oder die Einschränkung bei der Ausführung auf zunächst zurückgestellt setzen).EINSCHRÄNKUNGEN FESTLEGENist nicht machbar).

Als Nächstes schauen wir uns einige Anwendungsfälle für aufschiebbare Einschränkungen an, gefolgt von einem Bonus, wenn Sie bestehende Einschränkungen untersuchen müssen.

Anwendungsfälle

Zirkelverweise

Der klassische Fall für verzögerte Einschränkungen ist das Erstellen von Zirkelverweisen. Ohne verzögerte Einschränkungen könnten Sie keine Zeile erstellen, die von einer anderen Zeile abhängt, die wiederum von der Existenz dieser Zeile abhängt. Mit verzögerten Einschränkungen können Sie beide Zeilen erstellen, wobei beide Einschränkungen am Ende der Transaktion überprüft werden.

Lassen Sie uns dazu ein kurzes Beispiel durcharbeiten:

BEGINNEN;ERSTELLEN TISCH "Ausgabe" ("Ausweis" Varchar(64) NICHT NULL PRIMÄR SCHLÜSSEL,„op_ed“ Varchar(64) NICHT NULL);ERSTELLEN TISCH "Geschichte" ("Ausweis" Varchar(64) NICHT NULL PRIMÄR SCHLÜSSEL,"Ausgabe" Varchar(64) NICHT NULL);ÄNDERN TISCH "Ausgabe" HINZUFÜGEN ZWANG „issue_op_ed_fkey“AUSLÄNDISCH SCHLÜSSEL („op_ed“) VERWEISE "Geschichte" ("Ausweis") AN LÖSCHEN BESCHRÄNKEN;ÄNDERN TISCH "Geschichte" HINZUFÜGEN ZWANG „story_issue_fkey“AUSLÄNDISCH SCHLÜSSEL ("Ausgabe") VERWEISE "Ausgabe" ("Ausweis") AN LÖSCHEN KASKADE;einfügen hinein "Ausgabe" Werte ('26e8vnnqxkQSVjqfkcrErw7kpr7', '26e8yamF07UtUe80ZO7G1OrzSPO');einfügen hinein "Geschichte" Werte ('26e8yamF07UtUe80ZO7G1OrzSPO', '26e8vnnqxkQSVjqfkcrErw7kpr7');ROLLBACK;

Während Sie die Tabellen erstellen können, führt der Versuch, Daten hinzuzufügen, zu einem Haken, da Sie kein Problem einfügen können, bevor Sie eine Story erstellt haben, die wiederum von einem vorhandenen Problem abhängt.

Bei aufgeschobenen Einschränkungen gibt es einen Ausweg:

BEGINNEN;ERSTELLEN TISCH "Ausgabe" ("Ausweis" Varchar(64) NICHT NULL PRIMÄR SCHLÜSSEL,„op_ed“ Varchar(64) NICHT NULL);ERSTELLEN TISCH "Geschichte" ("Ausweis" Varchar(64) NICHT NULL PRIMÄR SCHLÜSSEL,"Ausgabe" Varchar(64) NICHT NULL);ÄNDERN TISCH "Ausgabe" HINZUFÜGEN ZWANG „issue_op_ed_fkey“ AUSLÄNDISCH SCHLÜSSEL („op_ed“) VERWEISE "Geschichte" ("Ausweis") AN LÖSCHEN BESCHRÄNKENAUFSCHIEBBAR;ÄNDERN TISCH "Geschichte" HINZUFÜGEN ZWANG „story_issue_fkey“ AUSLÄNDISCH SCHLÜSSEL ("Ausgabe") VERWEISE "Ausgabe" ("Ausweis") AN LÖSCHEN KASKADE;SATZEINSCHRÄNKUNGEN„issue_op_ed_fkey“VERSCHOBEN;einfügen hinein "Ausgabe" Werte ('26e8vnnqxkQSVjqfkcrErw7kpr7', '26e8yamF07UtUe80ZO7G1OrzSPO');einfügen hinein "Geschichte" Werte ('26e8yamF07UtUe80ZO7G1OrzSPO', '26e8vnnqxkQSVjqfkcrErw7kpr7');- Überprüfen Sie die ausstehenden EinschränkungenSATZEINSCHRÄNKUNGEN„issue_op_ed_fkey“SOFORT;ROLLBACK;

Die einzige Änderung besteht darin, die Fremdschlüsselverknüpfung mit der Leitartikel-Story zu verschieben. Auf diese Weise können wir, sofern wir beide IDs kennen, das Problem und dann die Story einfügen, die ein vorhandenes Problem als Referenz enthält.

LäuftEINSCHRÄNKUNGEN FESTLEGENDas erneute Ändern der Einschränkung auf „Sofort“ hat den gleichen Effekt wie das Erreichen des Endes der Transaktion und das Durchführen von Einschränkungsprüfungen, um Verstöße gegen die Fremdschlüsselintegrität zu finden.

Ineinander verschlungene Löschungen

Stellen Sie sich das folgende Beispiel vor, lesen Sie es ein paar Mal durch und versuchen Sie, das Problem zu finden.

BEGINNEN;ERSTELLEN TISCH „org“ ("Ausweis" Varchar(64) NICHT NULL PRIMÄR SCHLÜSSEL);ERSTELLEN TISCH "Team" ("Ausweis" Varchar(64) NICHT NULL PRIMÄR SCHLÜSSEL,„org“ Varchar(64) NICHT NULL VERWEISE „org“ ("Ausweis") AN LÖSCHEN KASKADE);ERSTELLEN TISCH "Mitglied" ("Ausweis" Varchar(64) NICHT NULL PRIMÄR SCHLÜSSEL,„org“ Varchar(64) NICHT NULL VERWEISE „org“ ("Ausweis") AN LÖSCHEN KASKADE,"Team" Varchar(64) NICHT NULL VERWEISE "Team" ("Ausweis") AN LÖSCHEN BESCHRÄNKEN);einfügen hinein „org“ Werte ('26eAkCctuBEztOlCJjWq9jF9A4f');einfügen hinein "Team" Werte ('26eAmEiPSIWyMt1twSpmFzsBGDM', '26eAkCctuBEztOlCJjWq9jF9A4f');einfügen hinein "Mitglied" Werte ('26eAoIGgQT0WKSHlEqrsNASJ0bK', '26eAkCctuBEztOlCJjWq9jF9A4f', '26eAmEiPSIWyMt1twSpmFzsBGDM');löschen aus „org“;ROLLBACK;

Das Problem hierbei ist, dass Teammitglieder darauf angewiesen sind, einem Team anzugehören, sodass das Löschen eines Teams, dem noch Mitglieder zugewiesen sind, fehlschlägt. Das ist normalerweise eine tolle Sache, aber wenn Sie eine Organisation löschen, werden ohnehin sowohl Teams als auch Mitglieder gelöscht. Möchten Sie diese Einschränkung also weiterhin?

Interessanterweise entscheidet die Art und Weise, wie PostgreSQL die Löschabfrage plant, darüber, ob alle Entitäten gelöscht werden oder ein Fehler auftritt. Wenn das Team zuerst fallen gelassen wird, schlägt es fehl. Wenn die Mitglieder zuerst gelöscht werden, ist dies erfolgreich.

Eine Möglichkeit, dieses Problem zu lösen, besteht darin, den Verweis auf das Team eines Mitglieds zu verschieben. Wir müssen auch die Aktion ändernBESCHRÄNKENZuKEINE AKTION, alsBESCHRÄNKENkann nicht zurückgestellt werden und würde trotzdem fehlschlagen, selbst wenn wir die Fremdschlüsseleinschränkung zurückstellen würden!

ändern Tisch "Mitglied" fallen Zwang „member_team_fkey“;ändern Tisch "Mitglied"hinzufügen Zwang „member_team_fkey“ausländisch Schlüssel ("Team")Verweise "Team" ("Ausweis")An löschen NEIN Aktionaufschiebbar zunächst aufgeschoben;

Auf diese Weise können wir die Organisation aufgeben, ohne uns Gedanken über Verletzungen der Fremdschlüsselintegrität machen zu müssen.

Einschränkungskonfiguration prüfen

Wenn Sie sich mit bestehenden Einschränkungen befassen, sind Sie möglicherweise neugierig auf die aktuelle Konfiguration: Ist die Fremdschlüsseleinschränkung aufschiebbar? Wird es sofort verschoben?

Dafür ist dieInformationsschemakann nützlich sein,zeigenDuist_aufschiebbarUndinitial_deferred(Notiz:durchgesetztist in PostgreSQL nicht und immer verfügbarJA).

wählen * ausinformationsschema.table_constraintsWoEinschränkungstyp= 'UNBEKANNTER SCHLÜSSEL'UndConstraint_schema= 'öffentlich'-- und Einschränkungsname = '';