Create unique constraint with null columns

Exploit PostgreSQL 15: Null's distinct appeal

PostgreSQL 15 modernizes handling nulls in unique constraints with NULLS NOT DISTINCT clause. Now, NULL behaves like another distinct value, simplifying constraint management:

--  Adapt and overcome: NULLs are now somebodies in PG15
ALTER TABLE your_table
ADD CONSTRAINT your_constraint UNIQUE NULLS NOT DISTINCT (column1, column2, ...);


In PostgreSQL versions preceding 15, you must employ alternative strategies to respect uniqueness, including nulls.

Strategies for older PostgreSQL versions

The Coalesce: Substitute NULL, preserve uniqueness

The COALESCE function offers an alternative, replacing NULL with a constant, ensuring uniqueness:

-- Null's got a stunt double now
CREATE UNIQUE INDEX idx_uniq_coalesce
ON your_table ((COALESCE(your_nullable_column, 'unique_placeholder')));


A UUID serves a hardcoded substitute avoiding collisions. Add a CHECK constraint as a bodyguard, protecting against accidental placeholder insertions.

The Generator: Indexing returns

Generated columns can create a non-nullable shadow column, which transforms null values into a unique figure for indexing:

-- The power of transformation: NULLs shapeshift into constant
ALTER TABLE your_table
ADD your_generated_column AS (COALESCE(your_nullable_column, 'unique_placeholder'));
CREATE UNIQUE INDEX idx_uniq_generated
ON your_table (your_generated_column);


This indirect method fortifies unique constraint enforcement with data integrity armor.

The Overlapping partial index

With a sole nullable column, build the index, no sweat. As nullable columns multiply, the exercise becomes more intricate, less intuitive:

-- More columns, more problems
CREATE UNIQUE INDEX idx_uniq_partial
ON your_table (col1, col2)
WHERE col1 IS NOT NULL AND col2 IS NOT NULL;


When sculpting constraints with partial indexes, see if removing WHERE allows total index overlapping, boosting overall database fitness.

Real-world application

Removing surrogate keys: Implications

In database crafting, surrogate keys serve relational mapping. When constraints direclty reside on data columns, decipher the knock-on effects of removing such keys. Perhaps you demand only one row per user/recipe pair, regardless of MenuId status:

-- One chef, one recipe; we're not running a cooking show here!
CREATE UNIQUE INDEX idx_uniq_user_recipe
ON menu(user_id, recipe_id)
WHERE MenuId IS NULL;


Minimalist table definitions

Keeping table definitions minimal, centered around necessity is wisdom. Protracted tables can spawn ambiguous data and sabotage unique constraint stronghold. Maintain cohesiveness in the constraint, focused on nullifying duplicate user and recipe pair listings, with MenuId disregard.

Foreign keys: A double-edged sword

Foreign keys play an instrumental role in preserving data integrity, and interfacing tables. However, when orchestrating unique constraints involving foreign keys, partial indexes can sway, testing balance.