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

Hello

2011/11/27 Mike Christensen <mike(at)kitchenpc(dot)com>:
> 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);

just idea

add index

CREATE UNIQUE INDEX foo_idx ON Favorites(UserId, Recipiend) WHERE MenuId IS NULL

Regards

Pavel Stehule

>
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>