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?

Edit
Report