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

[Thomas Kellerer]

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