- Published on
Partial unique index as a workaround for the nonexistent partial unique constraint
Recently, I had to enforce the uniqueness of certain row values that meet certain conditions. First solution which came to my mind was to use
unique constraint. Unfortunately, there is no something like
partial (or conditional) unique constraint. After a quick research I realised that the fact that
unique constraints are implemented using
unique indexes might be useful here.
PostgreSQL documentationAccording to the
A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries only for those table rows that satisfy the predicate. Partial indexes are a specialized feature, but there are several situations in which they are useful.
When an index is declared unique, multiple table rows with equal indexed values are not allowed. Null values are not considered equal. A multicolumn unique index will only reject cases where all indexed columns are equal in multiple rows.
Having that I decided to give partial unique indexes a try!
Let’s say that we have a
products table where we store information about all products available in our store (in the demo example we have just two columns →
status). In this example, we want to
enforce the uniqueness of product names within all
// create enum create type product_status as enum ('active', 'inactive'); // create table create table products ( name text, status product_status ); // add partial unique index to satisfy the requirement create unique index unique_product_name on products name where status = 'active';
Now we are sure that the names of all active products are unique.
Why the title of this article stands that this solution is a workaround? There is no official documentation for it and I am not sure if it’s the best approach to solve this issue. Nevertheless, it worked in my case and I didn’t face any issues connected with it yet.