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

On Nov 27, 2011, at 17:43, Mike Christensen <mike(at)kitchenpc(dot)com> wrote:

> 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

You agree it is the right thing to do but claim doing so is difficult while then immediately stating that creating and looking up the default uuid is not a huge deal...

Any query using this table's menuid is going to be suspect since it can be null. Since you could be linking the Menu table to it any menu oriented query is now suspect. Aggregate queries using this column are as well. Dealing with two where clauses (one with the IS NULL construct and one without) introduces uncertainty as well.

You are introducing a state of a user's recipe as being on or off menu when it is a much simpler model to just say every user recipe must be on at least one menu. Period. Achieving that simplicity is not difficult and so, for me at least, I'd need convincing as to why not to do it.

David J.