Wie erstelle ich eine eindeutige Einschränkung in Postgres mit Nullable-Spalten?

Umgang mit eindeutigen Einschränkungen mit Nullable-Spalten in PostgreSQL

Dieses Dokument befasst sich mit der Herausforderung der Durchsetzung eindeutiger Einschränkungen in PostgreSQL-Tabellen, die nullfähige Spalten enthalten. Das Problem entsteht, weil NULL-Werte standardmäßig als voneinander verschieden betrachtet werden. Dies ermöglicht mehrere Zeilen mit identischen Nicht-NULL-Werten, wenn eine NULL-fähige Spalte NULL enthält. Die Lösung zielt darauf ab, eine eindeutige Einschränkung zu schaffen, die NULL-Werte als gleichwertig behandelt.

Das Problem angehen

Das Ziel besteht darin, eine eindeutige Einschränkung zu erstellen, die doppelte Zeilen basierend auf bestimmten Spalten verhindert, selbst wenn nullfähige Spalten NULL-Werte enthalten.

Lösungen

PostgreSQL 15 und höher:

PostgreSQL 15 führte die NULLS NOT DISTINCT-Klausel ein und bot eine unkomplizierte Lösung. Diese Klausel stellt sicher, dass NULL-Werte innerhalb der Eindeutigkeitsbeschränkung als identisch betrachtet werden. Der folgende Befehl demonstriert seine Verwendung:

<code class="language-sql">ALTER TABLE favorites
ADD CONSTRAINT favo_uni UNIQUE NULLS NOT DISTINCT (user_id, menu_id, recipe_id);</code>

PostgreSQL 14 und früher:

Für ältere PostgreSQL-Versionen ist die NULLS NOT DISTINCT-Klausel nicht verfügbar. Die Lösung erfordert die Erstellung von zwei partiellen eindeutigen Indizes:

<code class="language-sql">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;</code>

Dieser Ansatz erzwingt effektiv die Einzigartigkeit. Der erste Index behandelt Fälle, in denen menu_id nicht NULL ist, während der zweite Index Situationen behandelt, in denen menu_id NULL ist. Dies verhindert doppelte Kombinationen von (user_id, recipe_id), wenn menu_id NULL ist.

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