SQL Postgres 唯一约束与索引

Postgres unique constraint vs index

提问by Adam Piotrowski

As I can understand documentationthe 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.4says:

但是，Postgres 9.4 手册中的注释说：

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.

向表添加唯一约束的首选方法是ALTER TABLE ... ADD CONSTRAINT. 使用索引来强制唯一约束可以被视为不应直接访问的实现细节。

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

（编辑：此注释已从 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)?

仅仅是风格的问题吗？选择这些变体之一的实际后果是什么（例如在性能方面）？

采纳答案by klin

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

我对这个基本但重要的问题有些怀疑，所以我决定以身作则。

Let's create test table masterwith two columns, con_idwith unique constraint and ind_idindexed by unique index.

让我们来创建测试表主有两列，CON_ID具有独特的约束和ind_id通过唯一索引收录。

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.

在表描述（psql 中的\d）中，您可以从唯一索引中分辨出唯一约束。

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 detailtable with two foreign keys referencing to our two columns in master.

现在我们将定义带有两个外键的详细表，这些外键引用我们在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 definitionof create table .... In unique index declaration you can set WHERE clauseto create partial index. You can also create indexon expression (not only on column) and define some other parameters (collation, sort order, NULLs placement).

在表约束声明中，您不能创建部分索引。它直接由来自定义的create table ...。在唯一索引声明中，您可以设置WHERE clause创建部分索引。您还可以在表达式上创建索引（不仅在列上）并定义一些其他参数（排序规则、排序顺序、NULL 放置）。

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.

回答by Vadim Zingertal

One more advantage of using UNIQUE INDEXvs. UNIQUE CONSTRAINTis that you can easily DROP/CREATEan index CONCURRENTLY, whereas with a constraint you can't.

使用UNIQUE INDEXvs. 的另一个优点UNIQUE CONSTRAINT是您可以轻松地使用DROP/CREATE索引CONCURRENTLY，而使用约束则不能。

回答by Eugen Konkov

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

所以速度性能应该是一样的

回答by ???

Another thing I've encountered is that you can use sql expressions in unique indexes but not in constraints.

我遇到的另一件事是您可以在唯一索引中使用 sql 表达式，但不能在约束中使用。

So, this does not work:

所以，这不起作用：

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

but following works.

但以下作品。

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

回答by Masklinn

Since various people have provided advantages of unique indexes over unique constraints, here's a drawback: a unique constraint can be deferred (only checked at the end of the transaction), a unique index can not be.

由于很多人都提供了唯一索引优于唯一约束的优点，这里有一个缺点：唯一约束可以延迟（仅在事务结束时检查），唯一索引不能。

回答by WesternGun

I read this in the doc:

我在文档中读到了这个：

ADD table_constraint [ NOT VALID ]

This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key constraints. If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates(that is, they'll fail unless there is a matching row in the referenced table). But the database will notassume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option.

添加 table_constraint [无效]

此表单使用与 相同的语法添加一个新的约束到表CREATE TABLE，加上选项NOT VALID，目前只允许用于外键约束。如果约束被标记NOT VALID，则跳过可能冗长的初始检查以验证表中的所有行是否满足约束。该约束仍将针对后续插入或更新强制执行（也就是说，除非引用表中有匹配的行，否则它们将失败）。但是数据库不会假设该约束适用于表中的所有行，直到使用 VALIDATE CONSTRAINT 选项对其进行验证。

So I think it is what you call "partial uniqueness" by adding a constraint.

所以我认为通过添加约束，这就是所谓的“部分唯一性”。

And, about how to ensure the uniqueness:

并且，关于如何确保唯一性：

Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint. A uniqueness restriction covering only some rows cannot be written as a unique constraint, but it is possible to enforce such a restriction by creating a unique partial index.

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.

添加唯一约束将自动在约束中列出的列或列组上创建唯一的 B 树索引。仅覆盖某些行的唯一性限制不能写为唯一性约束，但可以通过创建唯一部分索引来强制执行此类限制。

注意：向表添加唯一约束的首选方法是 ALTER TABLE ... ADD CONSTRAINT。使用索引来强制唯一约束可以被视为不应直接访问的实现细节。但是，应该注意没有必要在唯一的列上手动创建索引；这样做只会复制自动创建的索引。

So we should add constraint, which creates an index, to ensure uniqueness.

所以我们应该添加约束，它创建一个索引，以确保唯一性。

How I see this problem?

我怎么看这个问题？

A "constraint" aims to gramaticallyensure that this column should be unique, it establishes a law, a rule; while "index" is semantical, about "how to implement, how to achieve the uniqueness, what does unique means when it comes to implementation". So, the way Postgresql implements it, is very logical: first, you declare that a column should be unique, then, Postgresql adds the implementation of adding an unique index for you.

“约束”的目的是在语法上确保该列应该是唯一的，它建立了一条法律、一条规则；而“索引”是语义上的，关于“如何实现，如何实现唯一性，在实现时唯一意味着什么”。所以，Postgresql 的实现方式，是非常合乎逻辑的：首先，你声明一个列应该是唯一的，然后，Postgresql 为你添加了添加唯一索引的实现。

回答by Bax

There is a difference in locking.
Adding an index does not block read access to the table.
Adding a constraint does put a table lock (so all selects are blocked) since it is added via ALTER TABLE.

锁定是有区别的。
添加索引不会阻止对表的读取访问。
添加约束确实会放置一个表锁（因此所有选择都被阻止），因为它是通过ALTER TABLE添加的。