Wie erzwinge ich eindeutige Einschränkungen in PostgreSQL mit NULL-Spalten?

Eindeutige PostgreSQL-Einschränkungen und NULL-Spalten: Ein praktischer Leitfaden

Der Umgang mit eindeutigen Einschränkungen in PostgreSQL beim Umgang mit nullfähigen Spalten erfordert sorgfältige Überlegungen. Stellen Sie sich eine Tabelle mit den Spalten UserId, MenuId und RecipeId vor, wobei UserId und RecipeId ungleich Null sind. Eine standardmäßige Eindeutigkeitsbeschränkung für diese Spalten würde doppelte Einträge zulassen, wenn MenuId sich unterscheidet, einschließlich NULL-Werten.

Hier sind wirksame Strategien, um dieses Problem anzugehen:

PostgreSQL 15 und höhere Versionen

PostgreSQL 15 führte die NULLS NOT DISTINCT-Klausel ein und bot eine unkomplizierte Lösung. Diese Klausel behandelt NULL-Werte als gleich, wenn die Eindeutigkeit innerhalb von Einschränkungen und Indizes erzwungen wird:

<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ühere Versionen

Für ältere PostgreSQL-Versionen umfasst der empfohlene Ansatz die Erstellung von Teilindizes:

<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>

Dies erzwingt effektiv die Einzigartigkeit, indem separate Indizes für Zeilen erstellt werden, in denen menu_id NULL ist und in denen es NICHT NULL ist.

Wichtige Überlegungen zu Teilindizes

Die Verwendung von Teilindizes bringt einige Einschränkungen mit sich:

• Fremdschlüsseleinschränkungen können nicht direkt auf die Dreispaltenkombination (user_id, menu_id, recipe_id) angewendet werden.
• Clustering basierend auf Teilindizes wird nicht unterstützt.
• Abfragen ohne passende WHERE-Klauseln nutzen die Teilindizes nicht.

Best Practices:

Aus Gründen der Konsistenz und zur Vermeidung potenzieller Probleme ist es ratsam, in PostgreSQL Kleinbuchstaben-Bezeichner (z. B. favorites) zu verwenden. Die Wahl der geeigneten Methode hängt von Ihrer PostgreSQL-Version und Ihren spezifischen Anforderungen ab. Die NULLS NOT DISTINCT-Klausel bietet eine sauberere Lösung in neueren Versionen, während Teilindizes eine funktionale Alternative für ältere Versionen bieten.

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