Comment appliquer des contraintes uniques avec des colonnes nullables dans une table de base de données ?

Appliquer des contraintes uniques dans les tables contenant des colonnes nullables

Cet article aborde les défis liés à la création de contraintes uniques sur les tables contenant des colonnes nullables. L'objectif est d'empêcher plusieurs lignes d'avoir la même combinaison de valeurs, même si les colonnes nullables sont nulles.

Question :

Le schéma de table fourni a une colonne UserRepository nullable. Cependant, l'utilisateur souhaite s'assurer que pour chaque paire de UserId et RecipeId, une seule ligne existe, quelle que soit la valeur MenuId.

Réponse :

PostgreSQL 15 ou supérieur :

• Utilisez la clause NULLS NOT DISTINCT dans la définition de contrainte UNIQUE.

<code class="language-sql">ALTER TABLE Favorites
ADD CONSTRAINT Favorites_UniqueFavorite UNIQUE NULLS NOT DISTINCT (UserId, MenuId, RecipeId);</code>

PostgreSQL 14 ou version antérieure :

• Créez deux index partiels :

<code class="language-sql">CREATE UNIQUE INDEX favo_3col_uni_idx ON Favorites (UserId, MenuId, RecipeId)
WHERE MenuId IS NOT NULL;

CREATE UNIQUE INDEX favo_2col_uni_idx ON Favorites (UserId, RecipeId)
WHERE MenuId IS NULL;</code>

Avantages :

• Appliquer efficacement les contraintes requises.

Inconvénients (index partiel uniquement) :

• Limiter les références de clés étrangères et les options de clustering.
• Dans certaines requêtes, cela ne fonctionnera pas s'il n'y a pas de condition WHERE correspondante.

Alternative :

• Si plusieurs colonnes nullables sont impliquées, veuillez vous référer à la discussion dans cet article : "Pourquoi ma contrainte UNIQUE ne se déclenche-t-elle pas ?".

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!