How to Create a Unique Constraint in Postgres with Nullable Columns?

Handling Unique Constraints with Nullable Columns in PostgreSQL

This document addresses the challenge of enforcing unique constraints in PostgreSQL tables containing nullable columns. The issue arises because, by default, NULL values are considered distinct from each other. This allows multiple rows with identical non-NULL values when a nullable column holds NULL. The solution aims to create a unique constraint that treats NULLs as equivalent.

Addressing the Problem

The objective is to create a unique constraint preventing duplicate rows based on specific columns, even when nullable columns contain NULL values.

Solutions

PostgreSQL 15 and Later:

PostgreSQL 15 introduced the NULLS NOT DISTINCT clause, providing a straightforward solution. This clause ensures that NULL values are considered identical within the unique constraint. The following command demonstrates its use:

<code class="language-sql">ALTER TABLE favorites
ADD CONSTRAINT favo_uni UNIQUE NULLS NOT DISTINCT (user_id, menu_id, recipe_id);</code>

PostgreSQL 14 and Earlier:

For older PostgreSQL versions, the NULLS NOT DISTINCT clause is unavailable. The solution requires creating two partial unique indexes:

<code class="language-sql">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;</code>

This approach effectively enforces uniqueness. The first index handles cases where menu_id is not NULL, while the second index addresses situations where menu_id is NULL. This prevents duplicate combinations of (user_id, recipe_id) when menu_id is NULL.

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