Créer une contrainte unique avec des colonnes nulles

J’ai une table avec cette mise en page:

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 similaire à celle-ci:

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

Cependant, cela autorisera plusieurs lignes avec le même (UserId, RecipeId) , si MenuId IS NULL . Je veux autoriser NULL dans MenuId à stocker un favori qui n’a pas de menu associé, mais je ne veux au plus qu’une de ces lignes par paire utilisateur / recette.

Les idées que j’ai à ce jour sont les suivantes:

  1. Utilisez des UUID codés en dur (comme tous les zéros) au lieu de null.
    Cependant, MenuId a une contrainte FK sur les menus de chaque utilisateur, je devrais donc créer un menu spécial “null” pour chaque utilisateur, ce qui est compliqué.

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

  3. Il suffit de l’oublier et de vérifier l’existence préalable d’une entrée null dans le middleware 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?

Créez 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 façon, il ne peut y avoir qu’une combinaison de (user_id, recipe_id)menu_id IS NULL, implémentant efficacement la contrainte souhaitée.

Inconvénients possibles: vous ne pouvez pas avoir un référencement de clé étrangère (user_id, menu_id, recipe_id) cette manière, vous ne pouvez pas baser CLUSTER sur un index partiel, et les requêtes sans condition WHERE correspondante ne peuvent pas utiliser l’index partiel.

Il semble peu probable que vous souhaitiez une référence FK de trois colonnes de large (utilisez plutôt la colonne PK). Si vous avez besoin d’un index complet , vous pouvez également supprimer la condition WHERE de favo_3col_uni_idx et vos exigences sont toujours appliquées.
L’index, qui comprend désormais l’intégralité du tableau, chevauche l’autre et grossit. Selon les requêtes typiques et le pourcentage de valeurs NULL , cela peut ou peut ne pas être utile. Dans des situations extrêmes, il peut même être utile de conserver les deux versions de favo_3col_uni_idx .

Mis à part: je conseille de ne pas utiliser d’ identifiants de cas mixtes dans PostgreSQL .

Vous pouvez créer un index unique avec une coalescence sur 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 la COALESCE qui ne se produira jamais dans la “vraie vie”. Vous ne verriez probablement jamais un UUID nul dans la réalité, mais vous pouvez append une contrainte CHECK si vous êtes paranoïaque (et comme ils sont vraiment prêts à vous aider …):

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

Vous pouvez stocker les favoris sans menu associé dans une table distincte:

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

Je pense qu’il y a un problème sémantique ici. À mon avis, un utilisateur peut avoir (mais une seule ) recette préférée pour préparer un menu spécifique. (Le menu et la recette de l’OP sont mélangés; si je me trompe, veuillez échanger MenuId et RecipeId ci-dessous) Cela implique que {utilisateur, menu} devrait être une clé unique dans ce tableau. Et cela devrait indiquer exactement une recette. Si l’utilisateur n’a pas de recette préférée pour ce menu spécifique, aucune ligne ne doit exister pour cette paire de touches {utilisateur, menu}. De plus: la clé de substitution (FaVouRiteId) est superflue: les clés primaires composites sont parfaitement valables pour les tables de correspondance relationnelle.

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

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