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;