Deklarative Constraints in PostgreSQL: CHECK, UNIQUE und Fremdschlüssel mit ON DELETE

Worum es geht

Die PostgreSQL-Dokumentation zu Constraints beschreibt CHECK (ein je Zeile ausgewerteter Boolescher Ausdruck, erfüllt, wenn er wahr oder null ist), NOT NULL, UNIQUE (das NULL-Werte als unterschiedlich behandelt, sofern nicht NULLS NOT DISTINCT angegeben wird), PRIMARY KEY, EXCLUDE und FOREIGN KEY. Die referenzielle Aktion eines Fremdschlüssels legt fest, was beim Löschen der referenzierten Zeile geschieht: Der Standard NO ACTION löst einen Fehler aus, falls beim Prüfen des Constraints noch referenzierende Zeilen bestehen, was auf später in der Transaktion verschoben werden kann; RESTRICT verhindert das Löschen und kann nicht verschoben werden; CASCADE löscht die referenzierenden Zeilen; SET NULL und SET DEFAULT schreiben die referenzierenden Spalten um. Die Dokumentation hält fest, dass das Deklarieren eines Fremdschlüssels nicht automatisch einen Index auf den referenzierenden Spalten erzeugt.

Warum es wichtig ist

Constraints sind die einzige Validierung, die für jede schreibende Seite gilt: die Anwendung, eine psql-Sitzung, eine Migration, ein Massenimport. Ein CHECK, dass eine Menge positiv ist, oder ein UNIQUE auf einer E-Mail-Adresse entfernt eine ganze Fehlerklasse aus jedem Codepfad auf einmal, und der Fehler tritt bei der Anweisung auf, die ihn verursacht hat, statt Wochen später in einem Bericht.

So wird es angewendet

• Jede Spalte NOT NULL setzen, ausser «unbekannt» ist eine Bedeutung, die die Anwendung verarbeitet; CHECK-Constraints für Regeln hinzufügen, die sich nie ändern (price >= 0, starts_at < ends_at).
• ON DELETE je Beziehung festlegen: CASCADE für zugehörige Kindelemente (Bestellpositionen einer Bestellung), RESTRICT oder NO ACTION für Referenzen, die eine Person auflösen muss (ein Produkt, auf das noch Bestellungen verweisen), SET NULL für optionale Verknüpfungen.
• Die referenzierende Spalte jedes Fremdschlüssels indizieren, der in Joins verwendet wird oder dessen übergeordnete Zeilen gelöscht werden; ohne Index durchsucht ein Löschen der übergeordneten Zeile die Kindtabelle.
• Constraints benennen (CONSTRAINT orders_total_nonneg CHECK (...)), damit Fehlermeldungen und Migrationen lesbar bleiben.
• Fremdschlüssel-, CHECK- und Not-Null-Constraints auf grossen Tabellen als NOT VALID hinzufügen (die ALTER-TABLE-Dokumentation erlaubt die Option nur für diese Arten) und anschliessend VALIDATE CONSTRAINT ausführen, damit bestehende Zeilen geprüft werden, ohne die stärkste Sperre über den gesamten Scan zu halten.
• DEFERRABLE INITIALLY DEFERRED nur für Constraints verwenden, die innerhalb einer Transaktion vorübergehend verletzt sein müssen, etwa bei zyklischen Referenzen.

Stolpersteine

Ein CHECK kann nicht auf andere Zeilen oder Tabellen zugreifen; dafür EXCLUDE-Constraints oder Trigger verwenden. CASCADE an der falschen Beziehung löscht weit mehr als beabsichtigt, und das stillschweigend. Ein UNIQUE-Constraint auf einer nullfähigen Spalte erlaubt mehrere NULL-Werte, sofern nicht NULLS NOT DISTINCT angegeben ist. Nur im Anwendungscode geprüfte Constraints werden von jedem anderen Client umgangen.