Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postgres unique constraint vs index

As I can understand documentation the following definitions are equivalent:

create table foo (     id serial primary key,     code integer,     label text,     constraint foo_uq unique (code, label));  create table foo (     id serial primary key,     code integer,     label text); create unique index foo_idx on foo using btree (code, label);     

However, a note in the manual for Postgres 9.4 says:

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.

(Edit: this note was removed from the manual with Postgres 9.5.)

Is it only a matter of good style? What are practical consequences of choice one of these variants (e.g. in performance)?

like image 479
Adam Piotrowski Avatar asked May 08 '14 13:05

Adam Piotrowski


People also ask

Is index the same as unique constraint?

There is no difference between Unique Index and Unique Constraint. Even though syntax are different the effect is the same. Unique Constraint creates Unique Index to maintain the constraint to prevent duplicate keys.

Does unique constraint create index in Postgres?

PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table. The index covers the columns that make up the primary key or unique constraint (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint.

Does a unique constraint create an index?

Yes, absolutely. A unique constraint creates a unique index.

What is the difference between index and unique key?

Index: It is a schema object which is used to provide improved performance in the retrieval of rows from a table. Unique Index: Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns).


2 Answers

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. 
like image 121
klin Avatar answered Sep 22 '22 11:09

klin


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.

like image 39
Vadim Zingertal Avatar answered Sep 25 '22 11:09

Vadim Zingertal