Way to create unique constraint in Postgres even with null columns

Поиск
Список
Период
Сортировка
От Mike Christensen
Тема Way to create unique constraint in Postgres even with null columns
Дата
Msg-id CABs1bs3yFLC4ceskgXbzn1c1Z+uj891vXA0uftunpOjOb0cj6A@mail.gmail.com
обсуждение исходный текст
Ответы Re: Way to create unique constraint in Postgres even with null columns
Re: Way to create unique constraint in Postgres even with null columns
Список 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!

В списке pgsql-general по дате отправления:

Предыдущее
От: Richard Greenwood
Дата:
Сообщение: Re: text search synonym dictionary anomaly with numbers
Следующее
От: Pavel Stehule
Дата:
Сообщение: Re: Way to create unique constraint in Postgres even with null columns