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 1test=# 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 1test=# 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 indexLINE 1: alter table master add constraint master_part_id_key unique ...                               ^DETAIL:  Cannot create a primary key or unique constraint using such an index.

One more advantage of using UNIQUE INDEX vs. UNIQUE CONSTRAINT is that you can easily DROP/CREATE an index CONCURRENTLY, whereas with a constraint you can't.

Uniqueness is a constraint. It happens to be implemented via the creation of a unique index since an index is quickly able to search all existing values in order to determine if a given value already exists.

Conceptually the index is an implementation detail and uniqueness should be associated only with constraints.

The full text

So speed performance should be same