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

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Mike Christensen
Sent: Sunday, November 27, 2011 5:02 PM
To: Thomas Kellerer
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Way to create unique constraint in Postgres even with
null columns

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

-------------------------------------------------------------

While the conditional index will work this is one of many situations where
being explicit is probably the better option. It is quite likely that you
will want to use the MenuID in queries and having to deal with NULL in those
situations is messy. You should create a "DEFAULT" menu for each user and
replace any existing NULLs with the DEFAULT MenuID for the given user.
Then, make the MenuID column "NOT NULL".

Also, the index example above presumes you want RecipeId to be "Null-able"
as opposed to MenuId as described in your original post.

David J.