Way to create unique constraint in Postgres even with null columns

[Mike Christensen <mike@xxxxxxxxxxxxx>]

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 and
RecipeId, if the MenuId is null.  I want to allow a NULL 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 an existing 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 a insert function, and don't have
   this constraint.

Is there any method I'm forgetting?

I'm using Postgres 9.0.

Thanks!

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general