This may be a very silly question, but I have never used unique indexes: I have used unique constraints, though.
这可能是一个非常愚蠢的问题,但我从未使用过唯一的索引:我使用了独特的约束。
I read the related question Unique index vs. unique constraint that uses a unique index but still can't really understand why unique indexes exist.
我读了相关问题唯一索引与唯一约束,它使用唯一索引,但仍然无法真正理解为什么存在唯一索引。
To me UNIQUE sounds more like a constraint than as an index. Shouldn't it always be specified as a constraint? Or am I missing something here? (most likely). So in simple words the question I have is:
对我来说,UNIQUE听起来更像是一个约束而不是一个索引。它不应该总是被指定为约束吗?或者我在这里遗漏了什么? (最有可能的)。所以简单来说,我的问题是:
- What's the use of a unique index?
- 什么是使用独特的索引?
Maybe the answer lies in history books and is related on how they were chronologically developed.
也许答案在于历史书籍,并且与它们如何按时间顺序发展有关。
2 个解决方案
#1
2
One thing a unique index can do that a unique constraint cannot is given in the PostgreSQL documentation on unique constraints.
唯一索引可以做的一件事就是在PostgreSQL文档中对唯一约束不能给出唯一约束。
A uniqueness restriction covering only some rows cannot be written as a unique constraint, but it is possible to enforce such a restriction by creating a unique partial index.
仅覆盖某些行的唯一性限制不能写为唯一约束,但可以通过创建唯一的部分索引来强制执行此类限制。
In their partial unique index docs they give an example.
在他们的部分唯一索引文档中,他们举了一个例子。
Suppose that we have a table describing test outcomes. We wish to ensure that there is only one "successful" entry for a given subject and target combination, but there might be any number of "unsuccessful" entries. Here is one way to do it:
假设我们有一个描述测试结果的表。我们希望确保给定主题和目标组合只有一个“成功”条目,但可能存在任意数量的“不成功”条目。这是一种方法:
CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target) WHERE success;
CREATE UNIQUE INDEX tests_success_constraint ON测试(主题,目标)WHERE成功;
#2
1
In general, unique constraints are implemented using unique indexes (as far as I know, regardless of database). The differences between the two are pretty minor -- often having to do only with the name of the constraint in the event of a constraint violation. Some databases let you choose the type of index for a unique index, so that would be another slight difference.
通常,使用唯一索引实现唯一约束(据我所知,无论数据库如何)。两者之间的差异非常小 - 通常只有在违反约束的情况下才能使用约束的名称。有些数据库允许您为唯一索引选择索引类型,因此这将是另一个细微差别。
Why do the two exist? I can speculate. The designers of SQL could pretty much agree that the only reasonable way to implement a unique constraint is via a unique index. Given that CREATE INDEX
already existed, why not simply allow a unique option as well? After all, the database would need to support the ability to create a unique index.
为什么这两个存在?我可以推测。 SQL的设计者非常同意实现唯一约束的唯一合理方法是通过唯一索引。鉴于CREATE INDEX已经存在,为什么不简单地允许一个独特的选项呢?毕竟,数据库需要支持创建唯一索引的能力。
#1
2
One thing a unique index can do that a unique constraint cannot is given in the PostgreSQL documentation on unique constraints.
唯一索引可以做的一件事就是在PostgreSQL文档中对唯一约束不能给出唯一约束。
A uniqueness restriction covering only some rows cannot be written as a unique constraint, but it is possible to enforce such a restriction by creating a unique partial index.
仅覆盖某些行的唯一性限制不能写为唯一约束,但可以通过创建唯一的部分索引来强制执行此类限制。
In their partial unique index docs they give an example.
在他们的部分唯一索引文档中,他们举了一个例子。
Suppose that we have a table describing test outcomes. We wish to ensure that there is only one "successful" entry for a given subject and target combination, but there might be any number of "unsuccessful" entries. Here is one way to do it:
假设我们有一个描述测试结果的表。我们希望确保给定主题和目标组合只有一个“成功”条目,但可能存在任意数量的“不成功”条目。这是一种方法:
CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target) WHERE success;
CREATE UNIQUE INDEX tests_success_constraint ON测试(主题,目标)WHERE成功;
#2
1
In general, unique constraints are implemented using unique indexes (as far as I know, regardless of database). The differences between the two are pretty minor -- often having to do only with the name of the constraint in the event of a constraint violation. Some databases let you choose the type of index for a unique index, so that would be another slight difference.
通常,使用唯一索引实现唯一约束(据我所知,无论数据库如何)。两者之间的差异非常小 - 通常只有在违反约束的情况下才能使用约束的名称。有些数据库允许您为唯一索引选择索引类型,因此这将是另一个细微差别。
Why do the two exist? I can speculate. The designers of SQL could pretty much agree that the only reasonable way to implement a unique constraint is via a unique index. Given that CREATE INDEX
already existed, why not simply allow a unique option as well? After all, the database would need to support the ability to create a unique index.
为什么这两个存在?我可以推测。 SQL的设计者非常同意实现唯一约束的唯一合理方法是通过唯一索引。鉴于CREATE INDEX已经存在,为什么不简单地允许一个独特的选项呢?毕竟,数据库需要支持创建唯一索引的能力。