Créer une contrainte unique avec des colonnes nulles

Question

J'ai une table avec cette disposition :

CREATE TABLE Favorites
(
  FavoriteId uuid NOT NULL PRIMARY KEY,
  UserId uuid NOT NULL,
  RecipeId uuid NOT NULL,
  MenuId uuid
)

Je veux créer une contrainte unique semblable à celle-ci :

ALTER TABLE Favorites
ADD CONSTRAINT Favorites_UniqueFavorite UNIQUE(UserId, MenuId, RecipeId);

Cependant, cela permettra à plusieurs lignes avec le même nom d'utilisateur. (UserId, RecipeId) si MenuId IS NULL . Je veux permettre NULL en MenuId pour stocker un favori qui n'a pas de menu associé, mais je ne veux qu'un seul de ces rangs par couple utilisateur/préparation.

Les idées que j'ai jusqu'à présent sont :

1. Utilisez un UUID codé en dur (comme tous les zéros) au lieu de null.
   Cependant, MenuId a une contrainte FK sur les menus de chaque utilisateur, ce qui m'obligerait à créer un menu spécial "nul" pour chaque utilisateur, ce qui est fastidieux.

2. Vérifier l'existence d'une entrée nulle en utilisant un déclencheur à la place.
   Je pense que c'est un problème et j'aime éviter les déclencheurs autant que possible. De plus, je ne leur fais pas confiance pour garantir que mes données ne sont jamais dans un mauvais état.

3. Il suffit de l'oublier et de vérifier l'existence préalable d'une entrée nulle dans le middle-ware ou dans une fonction d'insertion, et de ne pas avoir cette contrainte.

J'utilise Postgres 9.0.

Y a-t-il une méthode que je néglige ?

Answer 1

Créer deux index partiels :

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;

De cette manière, il ne peut y avoir qu'une seule combinaison de (user_id, recipe_id) donde menu_id IS NULL ce qui permet de mettre en œuvre la contrainte souhaitée.

Inconvénients possibles :

• Vous ne pouvez pas avoir une clé étrangère faisant référence à (user_id, menu_id, recipe_id) . (Il semble peu probable que vous souhaitiez une référence FK de trois colonnes de large - utilisez plutôt la colonne PK).
• Vous ne pouvez pas vous baser CLUSTER sur un indice partiel.
• Requêtes sans correspondance WHERE ne peut pas utiliser l'indice partiel.

Si vous avez besoin d'un complet vous pouvez également supprimer l'indice WHERE l'état de favo_3col_uni_idx et vos exigences sont toujours appliquées.
L'index, qui comprend maintenant la totalité de la table, se superpose à l'autre et s'agrandit. En fonction des requêtes typiques et du pourcentage de NULL valeurs, cela peut être utile ou non. Dans des situations extrêmes, il peut même être utile de maintenir les trois index (les deux index partiels et un total en haut).

Il s'agit d'une bonne solution pour un une seule colonne nullable peut-être pour deux. Mais cela devient vite incontrôlable pour un plus grand nombre, car il faut un index partiel distinct pour chaque combinaison de colonnes annulables, ce qui fait que le nombre augmente de façon binomiale. Pour colonnes multiples annulables voir plutôt :

• Pourquoi ma contrainte UNIQUE ne se déclenche-t-elle pas ?

D'ailleurs, je conseille de ne pas utiliser Identifiants en casse mixte dans PostgreSQL .

Answer 2

Vous pourriez créer un index unique avec une coalescence sur le MenuId :

CREATE UNIQUE INDEX
Favorites_UniqueFavorite ON Favorites
(UserId, COALESCE(MenuId, '00000000-0000-0000-0000-000000000000'), RecipeId);

Il vous suffit de choisir un UUID pour le COALESCE qui ne se produira jamais dans la "vraie vie". Vous ne verrez probablement jamais un UUID nul dans la vie réelle, mais vous pourriez ajouter une contrainte CHECK si vous êtes paranoïaque (et puisque ils sont vraiment là pour vous attraper...) :

alter table Favorites
add constraint check
(MenuId <> '00000000-0000-0000-0000-000000000000')

Answer 3

Vous pouvez stocker les favoris sans menu associé dans un tableau séparé :

CREATE TABLE FavoriteWithoutMenu
(
  FavoriteWithoutMenuId uuid NOT NULL, --Primary key
  UserId uuid NOT NULL,
  RecipeId uuid NOT NULL,
  UNIQUE KEY (UserId, RecipeId)
)

Answer 4

Je pense qu'il y a un problème sémantique ici. A mon avis, un utilisateur peut avoir un (mais seulement un ) recette préférée pour préparer un menu spécifique. (L'OP a confondu menu et recette ; si je me trompe : veuillez interchanger MenuId et RecipeId ci-dessous) Cela implique que {user,menu} doit être une clé unique dans cette table. Et elle devrait pointer vers exactement un recette. Si l'utilisateur n'a pas de recette favorite pour ce menu spécifique aucune ligne ne devrait exister pour cette paire de clés {user,menu}. De plus, la clé de substitution (FaVouRiteId) est superflue : les clés primaires composites sont parfaitement valables pour les tables de mappage relationnel.

Cela conduirait à la définition réduite de la table :

CREATE TABLE Favorites
( UserId uuid NOT NULL REFERENCES users(id)
, MenuId uuid NOT NULL REFERENCES menus(id)
, RecipeId uuid NOT NULL REFERENCES recipes(id)
, PRIMARY KEY (UserId, MenuId)
);