Create unique constraint with null columns

Question

I have a table with this layout   CREATE TABLE Favorites     FavoriteId uuid NOT NULL PRIMARY KEY    UserId uuid NOT NULL    RecipeId uuid NOT NULL    MenuId uuid     I want to create a unique constraint similar to this   ALTER TABLE Favorites ADD CONSTRAINT Favorites UniqueFavorite UNIQUE UserId  MenuId  RecipeId     However  this will allow multiple rows with the same  UserId  RecipeId   if MenuId IS NULL   I want to allow NULL in MenuId to store a favorite that has no associated menu  but I only want at most one of these rows per user recipe pair   The ideas I have so far are    Use some hard-coded UUID  such as all zeros  instead of null  However  MenuId has a FK constraint on each user s menus  so I d then have to create a special  null  menu for every user which is a hassle  Check for existence of a null entry using a trigger instead  I think this is a hassle and I like avoiding triggers wherever possible  Plus  I don t trust them to guarantee my data is never in a bad state  Just forget about it and check for the previous existence of a null entry in the middle-ware or in a insert function  and don t have this constraint    I m using Postgres 9 0   Is there any method I m overlooking

User · Answer

You could create a unique index with a coalesce on the MenuId:

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

You'd just need to pick a UUID for the COALESCE that will never occur in "real life". You'd probably never see a zero UUID in real life but you could add a CHECK constraint if you are paranoid (and since they really are out to get you...):

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

User · Answer

You can store favourites with no associated menu in a separate table   CREATE TABLE FavoriteWithoutMenu     FavoriteWithoutMenuId uuid NOT NULL  --Primary key   UserId uuid NOT NULL    RecipeId uuid NOT NULL    UNIQUE KEY  UserId  RecipeId

User · Answer

Create two partial indexes   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    This way  there can only be one combination of  user id  recipe id  where menu id IS NULL  effectively implementing the desired constraint   Possible drawbacks  you cannot have a foreign key referencing  user id  menu id  recipe id   you cannot base CLUSTER on a partial index  and queries without a matching WHERE condition cannot use the partial index   It seems unlikely you d want a FK reference three columns wide - use the PK column instead    If you need a complete index  you can alternatively drop the WHERE condition from favo 3col uni idx and your requirements are still enforced  The index  now comprising the whole table  overlaps with the other one and gets bigger  Depending on typical queries and the percentage of NULL values  this may or may not be useful  In extreme situations it might even help to maintain all three indexes  the two partial ones and a total on top    Aside  I advise not to use mixed case identifiers in PostgreSQL

User · Answer

I think there is a semantic problem here  In my view  a user can have a  but only one  favourite recipe to prepare a specific menu   The OP has menu and recipe mixed up  if I am wrong  please interchange MenuId and RecipeId below  That implies that  user menu  should be a unique key in this table  And it should point to exactly one recipe  If the user has no favourite recipe for this specific menu no row should exist for this  user menu  key pair  Also  the surrogate key  FaVouRiteId  is superfluous  composite primary keys are perfectly valid for relational-mapping tables   That would lead to the reduced table definition   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

[sql] Create unique constraint with null columns

Examples related to sql

Examples related to postgresql

Examples related to database-design

Examples related to null

Examples related to referential-integrity