Create unique constraint with null columns

by

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:

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

More Related Contents:

  1. Select rows which are not present in other table
  2. Sort by column ASC, but NULL values first?
  3. How to implement a many-to-many relationship in PostgreSQL?
  4. Custom SERIAL / autoincrement per group of values
  5. Safely rename tables using serial primary key columns
  6. Best way to check for “empty or null value”
  7. Serial numbers per group of rows for compound key
  8. PostgreSQL: Which Datatype should be used for Currency?
  9. Sorting null values after all others, except special
  10. Sort NULL values to the end of a table
  11. Delete parent if it’s not referenced by any other child
  12. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  13. Postgres constraint for unique datetime range
  14. Select (retrieve) all records from multiple schemas using Postgres
  15. Which datatype should be used for currency?
  16. Combine two columns and add into one new column
  17. Use email address as primary key?
  18. IN Clause with NULL or IS NULL
  19. PostgreSQL Index on JSON
  20. Check if NULL exists in Postgres array
  21. Temporal database design, with a twist (live vs draft rows)
  22. PostgreSQL: running count of rows for a query ‘by minute’
  23. How to include “zero” / “0” results in COUNT aggregate?
  24. delete “column does not exist”
  25. Polymorphism in SQL database tables?
  26. postgresql: INSERT INTO … (SELECT * …)
  27. Return multiple columns and rows from a function PostgreSQL instead of record
  28. Postgres error updating column data
  29. SQL not displaying null values on a not equals query?
  30. SQL LIKE condition to check for integer?

Leave a Comment