How to Enforce Unique Constraints with Nullable Columns in a Database Table?

Enforce unique constraints in tables containing nullable columns

This article discusses the challenges of creating unique constraints on tables that contain nullable columns. The goal is to prevent multiple rows from having the same combination of values, even if the nullable columns are null.

Question:

The provided table schema has a nullable UserRepository column. However, the user wants to ensure that for each pair of UserId and RecipeId, only one row exists, regardless of the MenuId value.

Answer:

PostgreSQL 15 or higher:

• Use the NULLS NOT DISTINCT clause in the UNIQUE constraint definition.

<code class="language-sql">ALTER TABLE Favorites
ADD CONSTRAINT Favorites_UniqueFavorite UNIQUE NULLS NOT DISTINCT (UserId, MenuId, RecipeId);</code>

PostgreSQL 14 or lower:

• Create two partial indexes:

<code class="language-sql">CREATE UNIQUE INDEX favo_3col_uni_idx ON Favorites (UserId, MenuId, RecipeId)
WHERE MenuId IS NOT NULL;

CREATE UNIQUE INDEX favo_2col_uni_idx ON Favorites (UserId, RecipeId)
WHERE MenuId IS NULL;</code>

Advantages:

• Efficiently enforce required constraints.

Disadvantages (partial index only):

• Limit foreign key references and clustering options.
• In some queries, it will not work if there is no matching WHERE condition.

Alternative:

• If multiple nullable columns are involved, please refer to the discussion in this article: "Why is my UNIQUE constraint not firing?".

The above is the detailed content of How to Enforce Unique Constraints with Nullable Columns in a Database Table?. For more information, please follow other related articles on the PHP Chinese website!