On Sun, Nov 27, 2011 at 1:47 PM, Thomas Kellerer <spam_ea...@gmx.net> wrote: > 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;
Excellent solution! Thanks all.. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
