Create unique constraint with null columns

Question

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, RecipeId), if MenuId IS NULL. I want to allow NULL in 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 a 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 in a insert function, and don't have this constraint.

I'm using Postgres 9.0. Is there any method I'm overlooking?

Answer 1

Didn't find variant with partial index and NULLS NOT DISTINCT for PosgtreSQL 15+

To combine these options use such syntax

CREATE UNIQUE INDEX index_favorites_on_user_id_menu_id_recipe_id
ON favorites (user_id, menu_id, recipe_id)
NULLS NOT DISTINCT
WHERE state IN ('active', 'pending');

Answer 2

Postgres 15 or newer

Postgres 15 adds the clause NULLS NOT DISTINCT. The release notes:

• Allow unique constraints and indexes to treat NULL values as not distinct (Peter Eisentraut)

  Previously NULL values were always indexed as distinct values, but this can now be changed by creating constraints and indexes using UNIQUE NULLS NOT DISTINCT.

With this clause null is treated like just another value, and a UNIQUE constraint does not allow more than one row with the same null value. The task is simple now:

ALTER TABLE favorites
ADD CONSTRAINT favo_uni UNIQUE NULLS NOT DISTINCT (user_id, menu_id, recipe_id);

There are examples in the manual chapter "Unique Constraints".
The clause switches behavior for all keys of the same index. You can't treat null as equal for one key, but not for another.
NULLS DISTINCT remains the default (in line with standard SQL) and does not have to be spelled out.

The same clause works for a UNIQUE index, too:

CREATE UNIQUE INDEX favo_uni_idx
ON favorites (user_id, menu_id, recipe_id) NULLS NOT DISTINCT;

Note the position of the new clause after the key fields.

Postgres 14 or older

Create two partial indexes:

CREATE UNIQUE INDEX favo_3col_uni_idx ON favorites (user_id, menu_id, recipe_id)
WHERE menu_id IS NOT NULL;

CREATE UNIQUE INDEX favo_2col_uni_idx ON favorites (user_id, recipe_id)
WHERE menu_id IS NULL;

This way, there can only be one combination of (user_id, recipe_id) where menu_id IS NULL, effectively implementing the desired constraint.

Possible drawbacks:

• You cannot have a foreign key referencing (user_id, menu_id, recipe_id). (It seems unlikely you'd want a FK reference three columns wide - use the PK column instead!)
• You cannot base CLUSTER on a partial index.
• Queries without a matching WHERE condition cannot use the partial index.

If you need a complete index, you can alternatively drop the WHERE condition from favo_3col_uni_idx and your requirements are still enforced.
The index, now comprising the whole table, overlaps with the other one and gets bigger. Depending on typical queries and the percentage of null values, this may or may not be useful. In extreme situations it may even help to maintain all three indexes (the two partial ones and a total on top).

This is a good solution for a single nullable column, maybe for two. But it gets out of hands quickly for more as you need a separate partial index for every combination of nullable columns, so the number grows binomially. For multiple nullable columns, see instead:

• Why doesn't my UNIQUE constraint trigger?

Aside: I advise not to use mixed case identifiers in PostgreSQL.

Answer 3

I believe there is an option that combines the previous answers into a more optimal solution.

create table unique_with_nulls (
    id serial not null,
    name varchar not null,
    age int2 not null,
    email varchar,
    email_discriminator varchar not null generated always as ( coalesce(email::varchar, 0::varchar) ) stored,

    constraint uwn_pkey primary key (id)
);

create unique index uwn_name_age_email_uidx on unique_with_nulls(name, age, email_discriminator);

What happens here is that the column email_discriminator will be generated at "insert-or-update-time", as either an actual email, or "0" if the former one is null. Then, your unique index must target the discriminator column.
This way we don't have to create two partial indexes, and we don't loose the ability to use indexed scans on name and age selection only.
Also, you can keep the type of the email column and we don't have any problems with the coalesce function, because email_discriminator is not a foreign key. And you don't have to worry about this column receiving unexpected values because generated columns cannot be written to.

I can see three opinionated drawbacks in this solution, but they are all fine for my needs:

• the duplication of data between the email and email_discriminator.
• the fact that I must write to a column and read from another.
• the need to find a value that is outside the set of acceptable values of email to be the fallback one (and sometimes this could be hard to find or even subjective).

Answer 4

I think there is a semantic problem here. In my view, a user can have a (but only one) favourite recipe to prepare a specific menu. (The OP has menu and recipe mixed up; if I am wrong: please interchange MenuId and RecipeId below) That implies that {user,menu} should be a unique key in this table. And it should point to exactly one recipe. If the user has no favourite recipe for this specific menu no row should exist for this {user,menu} key pair. Also: the surrogate key (FaVouRiteId) is superfluous: composite primary keys are perfectly valid for relational-mapping tables.

That would lead to the reduced table definition:

CREATE TABLE Favorites
( UserId uuid NOT NULL REFERENCES users(id)
, MenuId uuid NOT NULL REFERENCES menus(id)
, RecipeId uuid NOT NULL REFERENCES recipes(id)
, PRIMARY KEY (UserId, MenuId)
);

Answer 5

You could create a unique index with a coalesce on the MenuId:

CREATE UNIQUE INDEX
Favorites_UniqueFavorite ON Favorites
(UserId, COALESCE(MenuId, '00000000-0000-0000-0000-000000000000'), RecipeId);

You'd just need to pick a UUID for the COALESCE that will never occur in "real life". You'd probably never see a zero UUID in real life but you could add a CHECK constraint if you are paranoid (and since they really are out to get you...):

alter table Favorites
add constraint check
(MenuId <> '00000000-0000-0000-0000-000000000000')

Answer 6

You can store favourites with no associated menu in a separate table:

CREATE TABLE FavoriteWithoutMenu
(
  FavoriteWithoutMenuId uuid NOT NULL, --Primary key
  UserId uuid NOT NULL,
  RecipeId uuid NOT NULL,
  UNIQUE KEY (UserId, RecipeId)
)