On Sun, Nov 27, 2011 at 2:18 PM, David Johnston <polobo@yahoo.com> wrote:
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Mike Christensen
> Sent: Sunday, November 27, 2011 5:02 PM
> To: Thomas Kellerer
> Cc: pgsql-general@postgresql.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@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..
>
> -------------------------------------------------------------
>
> 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
