On Sun, Nov 27, 2011 at 2:18 PM, David Johnston <polobo@xxxxxxxxx> wrote: > -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx > [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Mike Christensen > Sent: Sunday, November 27, 2011 5:02 PM > To: Thomas Kellerer > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: Way to create unique constraint in Postgres even with > null columns > > On Sun, Nov 27, 2011 at 1:47 PM, Thomas Kellerer <spam_eater@xxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
