Postgres vs index unique limitation

Question

As I can understand the 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, you can read in a note: the preferred way to add a unique constraint to the table is ALTER TABLE ... ADD CONSTRAINT. Using indexes to provide unique constraints can be seen as part of an implementation that should not be addressed directly.

Is this just a matter of good style? What are the practical implications of choosing one of these options (for example, at work)?

Answer 1

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

Let me create a test table wizard with two columns, con_id with a unique constraint and an index ind_id indexed by a 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 the table description (\ d in psql) you can specify a unique constraint from a unique index.

Uniqueness

Let it verify 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 will define a detailed table with two foreign keys referencing 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 mistakes. Let it work.

 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 refer to foreign keys.

Index Constraint

You can add a table constraint using an 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 the description of column restrictions.

Partial indexes

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

You cannot add a table constraint using a 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. 

Answer 2

Another advantage of using UNIQUE INDEX vs. UNIQUE CONSTRAINT is that you can easily DROP / CREATE specify a CONCURRENTLY index, whereas with a constraint you cannot.

Answer 3

Uniqueness is a limitation. This is accomplished through the creation of a unique index, because the index can quickly look for all existing values ​​to determine if a given value already exists.

Conceptually, an index is an implementation detail, and uniqueness should only be related to limitations.

Full text

So speed should be the same

Answer 4

Another thing that I came across is that you can use sql expressions in unique indexes, but not in restrictions.

So this does not work:

 CREATE TABLE users ( name text, UNIQUE (lower(name)) ); 

but the following works.

 CREATE TABLE users ( name text ); CREATE UNIQUE INDEX uq_name on users (lower(name)); 

Answer 5

I read this in the document:

ADD table_constraint [NOT VALID]

This form adds a new constraint to the table using the same syntax as CREATE TABLE , plus the NOT VALID parameter, which is currently only allowed for foreign key constraints. If a constraint is labeled NOT VALID , a potentially lengthy initial check to ensure that all rows in the table satisfy the constraint is skipped . The restriction will continue to apply to subsequent insertions or updates (that is, they will fail if there is no corresponding row in the reference table). But the database will not assume that the constraint is met for all rows in the table until it is checked using the VALIDATE CONSTRAINT parameter.

Therefore, I think that this is what you call "partial uniqueness", adding a constraint.

And about how to ensure uniqueness:

Adding a unique constraint will automatically create a unique B-tree index for the column or group of columns listed in the constraint. A uniqueness constraint that covers only some rows cannot be written as a unique constraint, but you can apply this constraint by creating a unique partial index.

Note. The preferred way to add a unique constraint to a table is ALTER TABLE ... ADD CONSTRAINT. Using indexes to provide unique constraints can be seen as an implementation detail that cannot be accessed directly. However, remember that there is no need to manually create indexes for unique columns; it just duplicates the automatically generated index.

Therefore, we must add the constraint that the index creates to ensure uniqueness.

How do I see this problem?

The “restriction” is aimed at grammatically guaranteeing that this column must be unique; it establishes a law, a rule; while “index” is semantic, about “how to implement, how to achieve uniqueness, which means uniqueness when it comes to implementation”. So the way Postgresql implements this is very logical: first you declare the column to be unique, then Postgresql adds the implementation of adding a unique index for you .

Answer 6

There is a difference in blocking.
Adding an index does not block read access to the table.
Adding a constraint sets the table lock, so all selections are locked.