Wie erstelle ich eindeutige Einschränkungen mit Nullable-Spalten in PostgreSQL?

Eindeutige Einschränkungen und Nullable-Spalten in PostgreSQL verwalten

PostgreSQL stellt besondere Herausforderungen dar, wenn es um die Durchsetzung von Eindeutigkeitsbeschränkungen für Spalten geht, die NULL-Werte zulassen. Mehrere Zeilen können identische Nicht-NULL-Werte haben und sich nur in ihren NULL-Einträgen unterscheiden. Dies erfordert einen sorgfältigen Umgang mit NULL-Werten innerhalb der Einschränkungsdefinition.

PostgreSQL 15 und höher: Die NULLS NOT DISTINCTLösung

PostgreSQL 15 und nachfolgende Versionen vereinfachen diesen Prozess mit der NULLS NOT DISTINCT-Klausel. Wenn diese Klausel zu einer UNIQUE-Einschränkung hinzugefügt wird, behandelt sie NULL-Werte als gleichwertig. Daher kann nur eine Zeile mit einer bestimmten Kombination von Nicht-NULL-Werten existieren, unabhängig davon, ob die nullbare Spalte NULL enthält.

ALTER TABLE favorites ADD CONSTRAINT favo_uni UNIQUE NULLS NOT DISTINCT (user_id, menu_id, recipe_id);

PostgreSQL 14 und früher: Teilindizes als Workaround

Für ältere PostgreSQL-Versionen besteht der empfohlene Ansatz darin, Teilindizes zu erstellen. Diese Indizes erzwingen die Eindeutigkeit von Teilmengen der Daten und verarbeiten NULL-Werte effektiv separat. Ein Index deckt Zeilen ab, in denen die nullbare Spalte NICHT NULL ist, und ein anderer behandelt Zeilen, in denen sie NULL ist.

CREATE UNIQUE INDEX favo_3col_uni_idx ON favorites (user_id, menu_id, recipe_id) WHERE menu_id IS NOT NULL;
CREATE UNIQUE INDEX favo_2col_uni_idx ON favorites (user_id, recipe_id) WHERE menu_id IS NULL;

Wichtige Überlegungen

Teilindizes sind zwar effektiv, können jedoch die Funktionalität einschränken. Sie können Fremdschlüsselreferenzierungs- und Clustering-Optionen einschränken. Wenn ein vollständiger Index unerlässlich ist, sollten Sie eine UNIQUE-Einschränkung in Betracht ziehen, die sich über einen breiteren Spaltensatz erstreckt, einschließlich der nullbaren Spalte.

Eine weitere Option, die möglicherweise für alle Situationen ungeeignet ist, besteht darin, der nullbaren Spalte einen Standardwert ungleich NULL zuzuweisen. Dies vereinfacht die Erstellung von Einschränkungen, kann jedoch je nach den Anforderungen der Anwendung Auswirkungen auf die Datenintegrität haben.

Schließlich sind konsistente Namenskonventionen entscheidend. Die Verwendung von Bezeichnern in Kleinbuchstaben in PostgreSQL verbessert die Lesbarkeit und vermeidet potenzielle Probleme.

Das obige ist der detaillierte Inhalt vonWie erstelle ich eindeutige Einschränkungen mit Nullable-Spalten in PostgreSQL?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!