Create unique constraint with null columns

by

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:

  1. 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.

  2. 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.

  3. 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?

4 Answers
4

Leave a Comment