Postgres唯一约束与索引
根据我对documentation的理解，以下定义是等价的：
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); 然而，the manual for Postgres 9.4中的一条注释说：
向表中添加唯一约束的首选方法是
ALTER TABLE ... ADD CONSTRAINT。使用索引来强制执行唯一约束可以被认为是不应该直接访问的实现细节。
(编辑:此注释已从Postgres 9.5的手册中删除。)
这仅仅是一个好风格的问题吗？选择这些变体中的一个有什么实际后果(例如，在性能上)？
回答 9
Stack Overflow用户
发布于 2014-05-15 05:53:18
我对这个基本但重要的问题有一些怀疑，所以我决定通过例子来学习。
让我们创建包含两列的测试表master，带有唯一约束的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)在表描述(psql中的\d)中，您可以区分唯一约束和唯一索引。
唯一性
让我们检查唯一性，以防万一。
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=#它就像预期的那样工作！
外键
现在我们将定义带有两个外键的细节表，这两个外键引用了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=#这两列都可以在外键中引用。
使用索引的约束
您可以使用现有的唯一索引添加表约束。
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)现在，列约束描述之间没有区别。
部分索引
在表约束声明中，不能创建分部索引。它直接来自create table ...的definition。在唯一索引声明中，您可以将WHERE clause设置为创建部分索引。您还可以对表达式执行create index操作(不仅针对列)，还可以定义一些其他参数(排序规则、排序顺序、NULL位置)。
您不能使用部分索引添加表约束。You 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.Stack Overflow用户
发布于 2015-06-06 21:06:26
与UNIQUE CONSTRAINT相比，使用UNIQUE INDEX的另一个优势是您可以轻松地对索引CONCURRENTLY执行DROP/CREATE操作，而使用约束则不能。
Stack Overflow用户
发布于 2017-10-20 20:13:08
唯一性是一个约束。它恰好是通过创建唯一索引来实现的，因为索引能够快速地搜索所有现有值，以便确定给定值是否已经存在。
从概念上讲，索引是一个实现细节，唯一性应该只与约束相关联。
所以速度性能应该是一样的
https://stackoverflow.com/questions/23542794
复制相似问题