Re: Way to create unique constraint in Postgres even with null columns

Mike Christensen wrote on 27.11.2011 22:18:
> 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.

In addition to the above unique constraint you will need another one:

CREATE UNIQUE INDEX Favorites_UniqueFavorite
    ON (UserId, MenuId)
    WHERE RecipeId IS NULL;