PostgreSQL CHECK-Constraints: Zeilenlokale Regeln und warum zeilenübergreifende Vergleiche Dumps zerstören
PostgreSQL CHECK-Constraints dürfen nur auf die eingefügte oder aktualisierte Zeile verweisen. Ein CHECK, der andere Zeilen vergleicht, kann einfache Tests bestehen, aber bei der Wiederherstellung mit pg_dump fehlschlagen, weil die Zeilen in einer Reihenfolge geladen werden, die ihn möglicherweise nicht erfüllt.
Auf dieser Seite
Kerngedanke
In PostgreSQL ist ein CHECK-Constraint ein boolescher Ausdruck, der ausschließlich gegen die neue oder aktualisierte Zeile ausgewertet wird. Er darf Spalten dieser Zeile, Konstanten, immutable Funktionen und Operatoren referenzieren, aber er darf nicht auf andere Zeilen oder andere Tabellen verweisen. PostgreSQL erzwingt diese Einschränkung nicht zum Definitionszeitpunkt, sodass ein zeilenübergreifender CHECK angelegt werden kann und in kleinen Tests zu funktionieren scheint. Er kann die Invariante jedoch nicht garantieren, weil spätere Änderungen an der referenzierten Zeile die Bedingung verfälschen können, ohne die ursprüngliche Zeile erneut zu prüfen. Die dokumentierte Konsequenz ist, dass ein Datenbank-Dump und dessen Wiederherstellung fehlschlagen können: Die Zeilen werden in einer Reihenfolge neu geladen, die den Constraint möglicherweise nicht erfüllt, selbst wenn der endgültige Datenbankzustand konsistent ist. Für zeilenübergreifende oder tabellenübergreifende Regeln sollten Sie UNIQUE-, EXCLUDE- oder FOREIGN-KEY-Constraints verwenden oder die Regel in der Anwendungslogik oder über einen Trigger durchsetzen. Denken Sie außerdem daran, dass ein CHECK als erfüllt gilt, wenn der Ausdruck NULL ergibt. Kombinieren Sie ihn daher mit NOT NULL, wenn Nullwerte ausgeschlossen werden müssen.
Zulässige CHECK-Constraints
Ein CHECK-Constraint ist in PostgreSQL der allgemeinste Constraint-Typ. Er hängt einen booleschen Ausdruck an eine Spalte oder an die Tabelle, und der Ausdruck wird bei jedem Einfügen oder Aktualisieren einer Zeile ausgewertet. Der Ausdruck sollte die eingeschränkte Spalte einbeziehen, andernfalls erfüllt er kaum einen Zweck. Spalten-Constraints und Tabellen-Constraints sind in vielen Fällen austauschbar, und ein Tabellen-Constraint kann mehrere Spalten derselben Zeile referenzieren.
Der Ausdruck darf Spalten der zu prüfenden Zeile, literale Konstanten, Operatoren und Funktionen verwenden. Er darf keine Tabellendaten außer der neuen oder aktualisierten Zeile referenzieren. Dies ist eine dokumentierte Einschränkung, keine stilistische Vorliebe. Der Constraint wird isoliert gegen die Kandidatenzeile geprüft, sodass er zum Auswertungszeitpunkt keinen Zugriff auf andere Zeilen hat.
Eine Feinheit, die viele Praktiker überrascht, ist die Behandlung von Nullwerten. Ein CHECK-Constraint gilt als erfüllt, wenn der Ausdruck true oder den Nullwert ergibt. Da die meisten Ausdrücke null ergeben, wenn ein Operand null ist, verhindert ein CHECK allein keine Nullwerte in den eingeschränkten Spalten. Um Nullwerte zu verbieten, fügen Sie einen NOT-NULL-Constraint hinzu, der funktional äquivalent zu CHECK (column IS NOT NULL) ist, in PostgreSQL aber effizienter.
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric,
CHECK (price > discounted_price)
);Das Risiko zeilenübergreifender Referenzen
PostgreSQL unterstützt keine CHECK-Constraints, die auf andere Tabellendaten als die neue oder aktualisierte Zeile verweisen. Die Dokumentation stellt ausdrücklich fest, dass ein CHECK, der diese Regel verletzt, in einfachen Tests zu funktionieren scheint, aber nicht garantieren kann, dass die Datenbank keinen Zustand erreicht, in dem die Constraint-Bedingung falsch ist. Der Grund ist, dass die Bedingung von anderen Zeilen abhängt und diese Zeilen sich ändern können, nachdem die ursprüngliche Zeile validiert wurde. Nichts prüft die ursprüngliche Zeile erneut, wenn die referenzierte Zeile geändert wird.
Dies ist ein Korrektheitsproblem, nicht bloß ein Performance-Problem. Ein Constraint, der nur zum Einfügezeitpunkt gilt, vermittelt ein falsches Gefühl von Integrität. Die Datenbank kann in einen Zustand abdriften, in dem die Invariante verletzt ist, ohne dass im Moment des Abdriften ein Fehler ausgelöst wird. Der Fehler zeigt sich später, oft zum ungünstigsten Zeitpunkt.
Dieselbe Argumentation gilt für Funktionen, die innerhalb eines CHECK verwendet werden. Eine Funktion, die andere Tabellen liest, führt dieselbe zeilenübergreifende Abhängigkeit ein, auch wenn die Syntax lokal aussieht. Die Einschränkung betrifft das, was der Ausdruck beobachten kann, nicht die Art und Weise, wie er geschrieben ist.
Fehler bei Dump und Wiederherstellung
Die dokumentierte Konsequenz eines zeilenübergreifenden CHECK ist, dass ein Datenbank-Dump und dessen Wiederherstellung fehlschlagen können. Während der Wiederherstellung werden Zeilen in einer durch den Dump bestimmten Reihenfolge geladen, und diese Reihenfolge erfüllt den Constraint möglicherweise nicht bei jedem Zwischenschritt. Die Wiederherstellung kann fehlschlagen, selbst wenn der vollständige Datenbankzustand mit dem Constraint konsistent ist, weil der Constraint Zeile für Zeile beim Einfügen der Daten ausgewertet wird.
Dies macht das Problem betrieblich ernst. Ein Backup, das sich in der Entwicklung sauber wiederherstellen lässt, kann in der Produktion fehlschlagen, wenn die Zeilenreihenfolge abweicht, wenn das Datenvolumen die Ladereihenfolge verändert oder wenn der Dump zu einem anderen Punkt im Datenlebenszyklus erstellt wurde. Der Fehler ist in Bezug auf den Endzustand nicht deterministisch; er hängt vom Pfad ab, der zu diesem Zustand führt.
Die praktische Lehre ist, dass ein Constraint, der nicht aus einer einzelnen Zeile heraus ausgewertet werden kann, nicht als deklarative Garantie verlässlich ist. Er kann Tests bestehen und sogar bei einer Gelegenheit eine Wiederherstellung überstehen, aber er bietet nicht die Integritätseigenschaft, die er zu bieten scheint.
Empfohlene Alternativen
Wenn eine Regel tatsächlich Zeilen oder Tabellen überspannt, bietet PostgreSQL deklarative Constraints, die für diesen Zweck entwickelt wurden. Verwenden Sie UNIQUE für Eindeutigkeit über Zeilen hinweg, EXCLUDE für Bereichs- und Überlappungsregeln und FOREIGN KEY für referenzielle Integrität. Diese Constraints werden von der Datenbank gegen die relevanten Zeilen durchgesetzt und bei Datenänderungen korrekt gepflegt.
Für Regeln, die keiner dieser Constraints ausdrückt, verwenden Sie einen Trigger oder eine Validierung auf Anwendungsebene und dokumentieren Sie klar, dass die Regel kein deklarativer Constraint ist. Ein Trigger kann andere Zeilen beobachten und so geschrieben werden, dass er bei den relevanten Änderungen erneut validiert, aber er bringt seine eigene Komplexität mit und muss sorgfältig gepflegt werden.
Ein nützliches mentales Modell ist die Frage, ob die Regel allein aus der Kandidatenzeile heraus entschieden werden kann. Wenn ja, ist ein CHECK angemessen und günstig. Wenn nein, gehört die Regel zu einem Constraint-Typ, der die Beziehung versteht, oder zu prozeduralem Code, den Sie als Durchsetzungspunkt akzeptieren.
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text NOT NULL,
price numeric NOT NULL CHECK (price > 0),
discounted_price numeric CHECK (discounted_price > 0),
CONSTRAINT valid_discount CHECK (price > discounted_price)
);Anwendungsbedingungen
- Referenziert der CHECK-Ausdruck nur Spalten der eingefügten oder aktualisierten Zeile?
- Betrifft die Regel andere Zeilen oder andere Tabellen, was stattdessen UNIQUE, EXCLUDE oder FOREIGN KEY erfordern würde?
- Sind nullbare Spalten mit NOT NULL kombiniert, wo Nullwerte ausgeschlossen werden müssen?
- Haben Sie einen Dump und eine Wiederherstellung der Tabelle getestet, um zu bestätigen, dass der Constraint das Neuladen übersteht?
- Ist der Constraint benannt, sodass er später identifiziert und geändert werden kann?
Geltungsbereich
Dieser Artikel beschreibt das PostgreSQL-Verhalten, wie es für unterstützte Versionen dokumentiert ist (14 bis 18 zum Zeitpunkt der Erstellung). Der genaue Wortlaut von Fehlermeldungen und das Verhalten von Dump und Wiederherstellung können je nach Version und verwendeten Werkzeugen (pg_dump, pg_restore oder logische Replikation) variieren. Die Beispiele sind illustrativ und setzen eine Standardinstallation ohne benutzerdefinierte Constraint-Trigger voraus. Der Artikel behandelt weder deferred Constraints noch Exclusion-Constraint-Operatoren oder triggerbasierte Durchsetzung im Detail; diese erfordern eine gesonderte Betrachtung. Er behauptet auch nicht, dass eine bestimmte Wiederherstellung fehlschlagen wird, sondern nur, dass ein zeilenübergreifender CHECK keine Integrität garantieren kann und je nach Zeilenladereihenfolge eine Wiederherstellung fehlschlagen lassen kann.