From: | Mike Christensen <mike(at)kitchenpc(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Way to create unique constraint in Postgres even with null columns |
Date: | 2011-11-27 21:18:09 |
Message-ID: | CABs1bs3yFLC4ceskgXbzn1c1Z+uj891vXA0uftunpOjOb0cj6A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2011-11-27 21:44:35 | Re: Way to create unique constraint in Postgres even with null columns |
Previous Message | Richard Greenwood | 2011-11-27 20:34:40 | Re: text search synonym dictionary anomaly with numbers |