Postgres唯一约束与索引 [英] 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);    

但是， Postgres 9.4手册说：

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

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.

（编辑：此注释在Postgres 9.5中已从手册中删除。）

( 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)?

推荐答案

我对此基本但重要的内容有些怀疑问题，所以我决定通过示例学习。

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

我们创建带有两列的 master 测试表 master ，唯一约束和 ind_id 由唯一索引索引。

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)

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

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

唯一性

为了以防万一，让我们检查支票的唯一性。

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=#

它按预期工作！

外键

现在，我们将定义 detail 表，其中两个外键引用我们在 master 中的两列。

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)

好，没有错误。

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.

使用索引约束

您可以使用现有唯一索引添加表约束。

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.

部分索引

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

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.

这篇关于Postgres唯一约束与索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！