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，con_id 带有唯一约束，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)

好吧，没有错误.让我们确保它有效.

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.

使用索引约束

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

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 table 的定义....在唯一索引声明中，您可以设置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屋！