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

Way to create unique constraint in Postgres even with null columns

From
Mike Christensen
Date:
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.

The ideas I have so far are:

 - 1) Use some hard-coded UUID (such as all zeros) instead of null.
   However, MenuId has a FK constraint on each user's menus, so I'd then
   have to create a special "null" menu for every user which is a
   hassle.
 - 2) Check for existence of an existing null entry using a trigger
   instead.  I think this is a hassle and I like avoiding triggers
   wherever possible.  Plus, I don't trust them to guarantee my data is
   never in a bad state.
 - 3) Just forget about it and check for the previous existence of a
   null entry in the middle-ware or a insert function, and don't have
   this constraint.

Is there any method I'm forgetting?

I'm using Postgres 9.0.

Thanks!

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

From
Pavel Stehule
Date:
Hello

2011/11/27 Mike Christensen <mike@kitchenpc.com>:
> 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);

just idea

add index

CREATE UNIQUE INDEX foo_idx ON Favorites(UserId, Recipiend) WHERE MenuId IS NULL

Regards

Pavel Stehule

>
> 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.
>
> The ideas I have so far are:
>
>  - 1) Use some hard-coded UUID (such as all zeros) instead of null.
>   However, MenuId has a FK constraint on each user's menus, so I'd then
>   have to create a special "null" menu for every user which is a
>   hassle.
>  - 2) Check for existence of an existing null entry using a trigger
>   instead.  I think this is a hassle and I like avoiding triggers
>   wherever possible.  Plus, I don't trust them to guarantee my data is
>   never in a bad state.
>  - 3) Just forget about it and check for the previous existence of a
>   null entry in the middle-ware or a insert function, and don't have
>   this constraint.
>
> Is there any method I'm forgetting?
>
> I'm using Postgres 9.0.
>
> Thanks!
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

From
Thomas Kellerer
Date:
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;




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

From
Mike Christensen
Date:
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..

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

From
"David Johnston"
Date:
-----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.

David J.


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

From
Mike Christensen
Date:
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

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

From
Thomas Kellerer
Date:
David Johnston wrote on 27.11.2011 23:18:

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

Well of course that was a typo in my answer, it should have been:

CREATE UNIQUE INDEX Favorites_UniqueFavorite
     ON (UserId, RecipeId)
     WHERE MenuId IS NULL;


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

From
David Johnston
Date:
On Nov 27, 2011, at 17:43, Mike Christensen <mike@kitchenpc.com> wrote:

> 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

You agree it is the right thing to do but claim doing so is difficult while then immediately stating that creating and
lookingup 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
tableto it any menu oriented query is now suspect.  Aggregate queries using this column are as well.  Dealing with two
whereclauses (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
everyuser 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.