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? 

我将从什么样的索引中获取独特的索引？假设只有一个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 9.4。我引用手册中的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 9.4. I quote the "ADD table_constraint_using_index" paragraph in the manual here:

索引不能包含表达式列，也不能是部分索引。另外，
它必须是具有默认排序顺序的b-tree索引。

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 命令，并按照链接了解更多信息。

  Other differences

  • 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 cannot reference columns with just a unique index. Per documentation:

    
    

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

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

  
  

  有关dba.SE的以后相关的更多详细信息：

  More details in this related later answer on dba.SE:

  
  
  • 当我需要使用运算符类的索引时，唯一索引优于唯一约束
  
  
  • Is unique index better than unique constraint when I need an index with an operator class

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