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