Hello
2011/11/27 Mike Christensen <mike@kitchenpc.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
