Postgres Unique Constraint VS Index

Postgres unique constraint vs index

I had some doubts about this basic but important issue, so I decided to learn by example.

Let's create test table master with two columns, con_id with unique constraint and ind_id indexed by unique index.

create table master (
    con_id integer unique,
    ind_id integer
);
create unique index master_unique_idx on master (ind_id);

    Table "public.master"
 Column |  Type   | Modifiers
--------+---------+-----------
 con_id | integer |
 ind_id | integer |
Indexes:
    "master_con_id_key" UNIQUE CONSTRAINT, btree (con_id)
    "master_unique_idx" UNIQUE, btree (ind_id)

In table description (\d in psql) you can tell unique constraint from unique index.

Uniqueness

Let's check uniqueness, just in case.

test=# insert into master values (0, 0);
INSERT 0 1
test=# insert into master values (0, 1);
ERROR:  duplicate key value violates unique constraint "master_con_id_key"
DETAIL:  Key (con_id)=(0) already exists.
test=# insert into master values (1, 0);
ERROR:  duplicate key value violates unique constraint "master_unique_idx"
DETAIL:  Key (ind_id)=(0) already exists.
test=#

It works as expected!

Foreign keys

Now we'll define detail table with two foreign keys referencing to our two columns in master.

create table detail (
    con_id integer,
    ind_id integer,
    constraint detail_fk1 foreign key (con_id) references master(con_id),
    constraint detail_fk2 foreign key (ind_id) references master(ind_id)
);

    Table "public.detail"
 Column |  Type   | Modifiers
--------+---------+-----------
 con_id | integer |
 ind_id | integer |
Foreign-key constraints:
    "detail_fk1" FOREIGN KEY (con_id) REFERENCES master(con_id)
    "detail_fk2" FOREIGN KEY (ind_id) REFERENCES master(ind_id)

Well, no errors. Let's make sure it works.

test=# insert into detail values (0, 0);
INSERT 0 1
test=# insert into detail values (1, 0);
ERROR:  insert or update on table "detail" violates foreign key constraint "detail_fk1"
DETAIL:  Key (con_id)=(1) is not present in table "master".
test=# insert into detail values (0, 1);
ERROR:  insert or update on table "detail" violates foreign key constraint "detail_fk2"
DETAIL:  Key (ind_id)=(1) is not present in table "master".
test=#

Both columns can be referenced in foreign keys.

Constraint using index

You can add table constraint using existing unique index.

alter table master add constraint master_ind_id_key unique using index master_unique_idx;

    Table "public.master"
 Column |  Type   | Modifiers
--------+---------+-----------
 con_id | integer |
 ind_id | integer |
Indexes:
    "master_con_id_key" UNIQUE CONSTRAINT, btree (con_id)
    "master_ind_id_key" UNIQUE CONSTRAINT, btree (ind_id)
Referenced by:
    TABLE "detail" CONSTRAINT "detail_fk1" FOREIGN KEY (con_id) REFERENCES master(con_id)
    TABLE "detail" CONSTRAINT "detail_fk2" FOREIGN KEY (ind_id) REFERENCES master(ind_id)

Now there is no difference between column constraints description.

Partial indexes

In table constraint declaration you cannot create partial indexes.
It comes directly from the definition of create table ....
In unique index declaration you can set WHERE clause to create partial index.
You can also create index on expression (not only on column) and define some other parameters (collation, sort order, NULLs placement).

You cannot add table constraint using partial index.

alter table master add column part_id integer;
create unique index master_partial_idx on master (part_id) where part_id is not null;

alter table master add constraint master_part_id_key unique using index master_partial_idx;
ERROR:  "master_partial_idx" is a partial index
LINE 1: alter table master add constraint master_part_id_key unique ...
                               ^
DETAIL:  Cannot create a primary key or unique constraint using such an index.

Unique constraint vs. unique index?

Yes, there's a small difference. If you define a unique constraint it's visible in catalogs like information_schema. This is not true of a unique index.

Also, you can create things like partial unique indexes, but you cannot do that on a constraint.

Finally, unique constraints are SQL-standard.

A unique constraint implies the creation of a unique index, but not vice versa.

Use a unique constraint unless you have a good reason to create the unique index directly.

Is any performance enhancement when we used Unique index instead of non Unique index?

An unique index won't be any faster to scan than a non-unique one. The only potential benefit in query execution speed could be that the optimizer can make certain deductions from the uniqueness and for example remove an unnecessary join.

The primary use of unique indexes is to implement table constraints, not to provide a performance advantage over non-unique indexes.

Here is an example:

CREATE TABLE parent (pid bigint PRIMARY KEY);

CREATE TABLE child (
   cid bigint PRIMARY KEY,
   pid bigint UNIQUE REFERENCES parent
);

EXPLAIN (COSTS OFF)
SELECT parent.pid FROM parent LEFT JOIN child USING (pid);

     QUERY PLAN     
════════════════════
 Seq Scan on parent
(1 row)

Without the unique constraint on child.pid (which is implemented by a unique index) the join could not be removed.

Should I specify both INDEX and UNIQUE INDEX?

If you have a UNIQUE INDEX then you don't also need the INDEX - it would be redundant. A UNIQUE INDEX is both a unique constraint and an index that can be used like any other index.

From the documentation:

Note: The preferred way to add a unique constraint to a table is ALTER TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique constraints could be considered an implementation detail that should not be accessed directly. One should, however, be aware that there's no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.

Emphasis mine.

Unexpected creation of duplicate unique constraints in Postgres

You can create multiple unique constraints on the same column as long as they have different names, simply because there is nothing in the PostgreSQL code that forbids that. Each unique constraint will create a unique index with the same name, because that is how unique constraints are implemented.

This can be a valid use case: for example, if the index is bloated, you could create a new constraint and then drop the old one.

But normally, it is useless and does harm, because each index will make data modifications on the table slower.

Postgres UNIQUE CONSTRAINT/INDEX for string array

The trigger solution is not transparent as it is actually modifying the data. Here is an alternative. Create array_sort helper function (it might be useful for other cases too) and an unique index using it.

create or replace function array_sort (arr anyarray) returns anyarray immutable as 
$$
  select array_agg(x order by x) from unnest(arr) x;
$$ language sql;    

create table t (arr integer[]);
create unique index tuix on t (array_sort(arr));

Demo

insert into t values ('{1,2,3}'); -- OK
insert into t values ('{2,1,3}'); -- unique violation
select * from t;

---