Thread: Way to create unique constraint in Postgres even with null columns
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!
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 >
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;
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..
-----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.
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
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;
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.