Comment créer des contraintes uniques avec des colonnes nullables dans PostgreSQL ?

Gestion des contraintes uniques et des colonnes nullables dans PostgreSQL

PostgreSQL présente des défis uniques lors de l'application de contraintes d'unicité sur les colonnes qui autorisent les valeurs NULL. Plusieurs lignes peuvent partager des valeurs non NULL identiques tout en différant uniquement par leurs entrées NULL. Cela nécessite une gestion minutieuse des valeurs NULL dans la définition de contrainte.

PostgreSQL 15 et versions ultérieures : la NULLS NOT DISTINCT solution

PostgreSQL 15 et les versions ultérieures simplifient ce processus avec la clause NULLS NOT DISTINCT. Cette clause, lorsqu'elle est ajoutée à une contrainte UNIQUE, traite les valeurs NULL comme équivalentes. Par conséquent, une seule ligne peut exister avec une combinaison donnée de valeurs non NULL, que la colonne nullable contienne ou non NULL.

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

PostgreSQL 14 et versions antérieures : index partiels comme solution de contournement

Pour les anciennes versions de PostgreSQL, l'approche recommandée consiste à créer des index partiels. Ces index imposent l'unicité sur des sous-ensembles de données, gérant efficacement les valeurs NULL séparément. Un index couvre les lignes où la colonne nullable n'est PAS NULL, et un autre gère les lignes où elle est NULL.

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;

Considérations importantes

Les index partiels, bien qu'efficaces, peuvent limiter les fonctionnalités. Ils peuvent restreindre les options de référencement de clé étrangère et de clustering. Si un index complet est essentiel, envisagez une contrainte UNIQUE couvrant un ensemble de colonnes plus large, y compris la colonne nullable.

Une autre option, bien que potentiellement inadaptée à toutes les situations, consiste à attribuer une valeur par défaut non NULL à la colonne nullable. Cela simplifie la création de contraintes mais pourrait avoir un impact sur l'intégrité des données en fonction des exigences de l'application.

Enfin, des conventions de dénomination cohérentes sont cruciales. L'utilisation d'identifiants minuscules dans PostgreSQL améliore la lisibilité et évite les problèmes potentiels.

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!