Comment appliquer des contraintes uniques dans PostgreSQL avec des colonnes NULL ?

Contraintes uniques PostgreSQL et colonnes NULL : un guide pratique

La gestion des contraintes uniques dans PostgreSQL lorsqu'il s'agit de colonnes nullables nécessite une attention particulière. Imaginez un tableau avec des colonnes UserId, MenuId et RecipeId, où UserId et RecipeId ne sont pas nuls. Une contrainte unique standard sur ces colonnes permettrait des entrées en double si MenuId diffère, y compris les valeurs NULL.

Voici des stratégies efficaces pour résoudre ce problème :

PostgreSQL 15 et versions ultérieures

PostgreSQL 15 a introduit la clause NULLS NOT DISTINCT, offrant une solution simple. Cette clause traite les valeurs NULL comme égales lors de l'application de l'unicité au sein des contraintes et des index :

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

PostgreSQL 14 et versions antérieures

Pour les anciennes versions de PostgreSQL, l'approche recommandée consiste à créer des index partiels :

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

Cela renforce efficacement l'unicité en créant des index séparés pour les lignes où menu_id est NULL et où il n'est PAS NULL.

Considérations importantes pour les index partiels

L'utilisation d'index partiels introduit certaines limitations :

• Les contraintes de clé étrangère ne peuvent pas être directement appliquées à la combinaison à trois colonnes (user_id, menu_id, recipe_id).
• Le clustering basé sur des index partiels n'est pas pris en charge.
• Les requêtes dépourvues de clauses WHERE correspondantes n'utiliseront pas les index partiels.

Bonnes pratiques :

Il est conseillé d'utiliser des identifiants minuscules (par exemple, favorites) dans PostgreSQL pour des raisons de cohérence et pour éviter des problèmes potentiels. Le choix de la méthode appropriée dépend de votre version de PostgreSQL et de vos besoins spécifiques. La clause NULLS NOT DISTINCT offre une solution plus propre dans les versions plus récentes, tandis que les index partiels offrent une alternative fonctionnelle pour les anciennes versions.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!