Hello 2011/11/27 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); 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
