Comment créer une contrainte unique dans Postgres avec des colonnes nullables ?

Gestion des contraintes uniques avec des colonnes nullables dans PostgreSQL

Ce document aborde le défi de l'application de contraintes uniques dans les tables PostgreSQL contenant des colonnes nullables. Le problème se pose car, par défaut, les valeurs NULL sont considérées comme distinctes les unes des autres. Cela autorise plusieurs lignes avec des valeurs identiques non NULL lorsqu'une colonne nullable contient NULL. La solution vise à créer une contrainte unique qui traite les NULL comme équivalents.

Résoudre le problème

L'objectif est de créer une contrainte unique empêchant les lignes en double basées sur des colonnes spécifiques, même lorsque les colonnes nullables contiennent des valeurs NULL.

Solutions

PostgreSQL 15 et versions ultérieures :

PostgreSQL 15 a introduit la clause NULLS NOT DISTINCT, offrant une solution simple. Cette clause garantit que les valeurs NULL sont considérées comme identiques dans la contrainte unique. La commande suivante démontre son utilisation :

<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, la clause NULLS NOT DISTINCT n'est pas disponible. La solution nécessite de créer deux index uniques 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>

Cette approche renforce efficacement l'unicité. Le premier index gère les cas où menu_id n'est pas NULL, tandis que le deuxième index traite les situations où menu_id est NULL. Cela évite les combinaisons en double de (user_id, recipe_id) lorsque menu_id est NULL.

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!