Postgres唯一约束与索引

根据我对文档的理解，以下定义是等价的:

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。使用索引来强制惟一的约束 可以被认为是一个不应该被认为是实现细节的实现吗 直接访问. < / p >

(编辑:此说明已从Postgres 9.5的手册中删除。)

仅仅是风格的问题吗?选择其中一个变量的实际后果是什么(例如在性能方面)?

119228 次浏览
小开
最佳答案

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

让我们创建带有两个列的测试表主，带有唯一约束的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=#

它像预期的那样工作!

外键

现在，我们将用两个外键定义细节表，引用主中的两列。

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 ...的定义。 在唯一索引声明中，可以设置WHERE clause来创建部分索引。 你也可以在表达式上创建索引(不仅仅是列上)，并定义一些其他参数(排序、排序顺序、空值位置)

你不能添加表约束使用部分索引。

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.
小开

与UNIQUE CONSTRAINT相比，使用UNIQUE INDEX的另一个优点是可以轻松地将索引CONCURRENTLY DROP/CREATE，而使用约束则不能。

小开

唯一性是一个约束。它恰好是通过创建来实现的 唯一的索引，因为一个索引能够很快地搜索所有现有的 值，以确定给定的值是否已经存在 从概念上讲，索引是一个实现细节，唯一性应该是

全文 .

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

小开

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

所以，这是行不通的:

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

但下面的做法是可行的。

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

我在医生里读到:

添加table_constraint [NOT VALID]

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

所以我认为这就是你所说的“部分独特性”。通过添加约束。

以及，关于如何确保唯一性:

添加唯一的约束将自动在约束中列出的列或列组上创建唯一的b -树索引。仅覆盖某些行的唯一性限制不能被写成惟一约束，但是可以通过创建惟一的部分索引来实施这样的限制。

注意:向表中添加唯一约束的首选方法是ALTER table…add constraint。使用索引来实施唯一的约束可以被认为是不应该直接访问的实现细节。但是，应该注意，没有必要在唯一的列上手动创建索引;这样做只会复制自动创建的索引。

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

我怎么看这个问题?

一个“constraint"旨在gramatically确保这一列应该是唯一的，它建立了一个法律，一个规则;而“index"是语义的，关于“如何实现，如何实现唯一性，当涉及到实现时，唯一性意味着什么”。所以，Postgresql实现它的方式是非常符合逻辑的:首先，你声明一个列应该是唯一的，然后，Postgresql为你添加一个唯一索引的实现。

小开

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

小开

由于许多人已经提供了唯一索引相对于唯一约束的优点，这里有一个缺点:唯一约束可以延迟(仅在事务结束时检查)，唯一索引不能。

小开

有一件非常小的事情只能使用约束而不能使用索引，那就是使用ON CONFLICT ON CONSTRAINT子句(还有这个问题)。

这行不通:

CREATE TABLE T (a INT PRIMARY KEY, b INT, c INT);
CREATE UNIQUE INDEX u ON t(b);


INSERT INTO T (a, b, c)
VALUES (1, 2, 3)
ON CONFLICT ON CONSTRAINT u
DO UPDATE SET c = 4
RETURNING *;

它产生:

[42704]: ERROR: constraint "u" for table "t" does not exist

将索引转换为约束:

DROP INDEX u;
ALTER TABLE t ADD CONSTRAINT u UNIQUE (b);

INSERT语句现在可以工作了。

小开 
SELECT a.phone_number,count(*) FROM public.users a
Group BY phone_number Having count(*)>1;


SELECT a.phone_number,count(*) FROM public.retailers a
Group BY phone_number Having count(*)>1;


select a.phone_number from users a inner join users b
on a.id <> b.id and a.phone_number = b.phone_number order by a.id;




select a.phone_number from retailers a inner join retailers b
on a.id <> b.id and a.phone_number = b.phone_number order by a.id

DELETE FROM
users a
USING users b
WHERE
a.id > b.id
AND a.phone_number = b.phone_number;
    

DELETE FROM
retailers a
USING retailers b
WHERE
a.id > b.id
AND a.phone_number = b.phone_number;
    

CREATE UNIQUE INDEX CONCURRENTLY users_phone_number
ON users (phone_number);

验证:

insert into users(name,phone_number,created_at,updated_at) select name,phone_number,created_at,updated_at from users

提交答案