PostgreSQL 如何强制执行 UNIQUE 约束/它使用什么类型的索引? [英] How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?

问题描述

阅读关于索引唯一性的文档是一个实现细节:

向表添加唯一约束的首选方法是 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. 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.

因此，按照文档的意思，我将仅将事物声明为唯一并使用隐式索引 - 或者 - 创建索引而不假设值是唯一的.这是一个错误吗?

So taking the docs at their word I'm going to just declare things as unique and use the implicit index - or - create an index and not assume that the values are unique. Is this a mistake? 

我将从unique得到什么样的索引?鉴于只有 btree 会接受唯一约束并且唯一隐式创建索引，UNIQUE 创建 btree 索引是真的吗?我不想无意中在哈希索引上运行范围.

What kind of index will I be getting from unique? Given that only a btree will accept the unique constraint and unique implicitly creates an index is it true that UNIQUE creates a btree index? I don't want to be running ranges on a hash index inadvertently. 

推荐答案

或者 - 创建一个索引并且不假设值是唯一的

or - create an index and not assume that the values are unique

如果您定义了唯一索引，则可以安全地假设值是唯一的.这就是唯一约束的实现方式(目前，并且可能在所有未来版本中也是如此).

It is safe to assume that values are unique, if you have a unique index defined. That's how unique constraints are implemented (at the time being, and probably in all future versions as well).

定义 UNIQUE 约束与创建唯一索引而不指定索引类型有效地相同(几乎见下文).而且，我引用手册:

Defining a UNIQUE constraint does effectively the same (almost, see below) as creating a unique index without specifying the index type. And, I quote the manual:

选择是 btree、hash、gist 和 gin.默认方法是btree.

Choices are btree, hash, gist, and gin. The default method is btree.

添加约束只是规范的方式，在未来的版本中不会中断，可以以不同的方式实现.仅此而已.

Adding a constraint is just the canonical way that would not break in future versions where it could be implemented differently. That's all.

不，唯一约束只能通过基本的btree索引在所有版本中实现，包括 PostgreSQL v14.我引用了ADD table_constraint_using_index"手册中的段落这里:

And no, a unique constraint can only be implemented with a basic btree index in all versions up to and including PostgreSQL v14. I quote the "ADD table_constraint_using_index" paragraph in the manual here:

索引不能有表达式列，也不能是部分索引.还，它必须是具有默认排序顺序的 b 树索引.

The index cannot have expression columns nor be a partial index. Also, it must be a b-tree index with default sort ordering.

###其他差异

• 独特的约束可以推迟.这对于唯一索引是不可能的.看看SET CONSTRAINTS 命令并点击链接了解更多信息.

• Unique constraints can be deferred. That is not possible for unique indexes. Have a look at the SET CONSTRAINTS command and follow the links for more.

外键必须引用作为主键或形成唯一约束.

A foreign key must reference columns that either are a primary key or form a unique constraint.

最后一点似乎已经过时或从一开始就存在误解.见:

The last bit seems to be outdated or a misunderstanding from the getgo. See:

• referential_constraints.unique_constraint_* 列中的 NULL 值信息架构

相关:

• 当我需要带有运算符类的索引时，唯一索引是否比唯一约束更好

这篇关于PostgreSQL 如何强制执行 UNIQUE 约束/它使用什么类型的索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！