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

On Sun, Nov 27, 2011 at 2:18 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:
> -----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.

Yea I assumed the idea was to do something like:

CREATE UNIQUE INDEX IDX_Favorites_NullMenu ON Favorites(UserId,
RecipeId) WHERE MenuId IS NULL;
CREATE UNIQUE INDEX IDX_Favorites_UniqueMenu ON Favorites(UserId,
RecipeId, MenuId) WHERE MenuId IS NOT NULL;

I'm not sure what situations become "messy" when MenuId allows NULLs
though. Maybe you can provide a few more details? I do agree
creating a default Menu for each user is the way to go. However, it
makes inserting a lot more difficult. If a user adds a recipe to
their favorites, and does not specify a menu, I then have to go lookup
the UUID for their default menu first. It's not a huge deal, but I'd
like to avoid that if possible. I think NULL makes sense here since
it really does mean "there is no menu for this row". Thanks!

Mike