[GENERAL] Way to create unique constraint in Postgres even with null columns

[Mike Christensen]

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!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Pavel Stehule]

Hello

2011/11/27 Mike Christensen m...@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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Thomas Kellerer]

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;





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Mike Christensen]

On Sun, Nov 27, 2011 at 1:47 PM, Thomas Kellerer spam_ea...@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..

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[David Johnston]

-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@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_ea...@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.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Mike Christensen]

On Sun, Nov 27, 2011 at 2:18 PM, David Johnston pol...@yahoo.com wrote:
 -Original Message-
 From: pgsql-general-ow...@postgresql.org
 [mailto:pgsql-general-ow...@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_ea...@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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Thomas Kellerer]

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;



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[David Johnston]

On Nov 27, 2011, at 17:43, Mike Christensen m...@kitchenpc.com wrote:

 On Sun, Nov 27, 2011 at 2:18 PM, David Johnston pol...@yahoo.com wrote:
 -Original Message-
 From: pgsql-general-ow...@postgresql.org
 [mailto:pgsql-general-ow...@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_ea...@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 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.
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general