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

On Sun, Nov 27, 2011 at 1:47 PM, Thomas Kellerer <spam_eater@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..