我应该同时指定INDEX和UNIQUE INDEX吗？ [英] Should I specify both INDEX and UNIQUE INDEX?

问题描述

在我的一个PostgreSQL表中，我有一组两个字段，将在表中定义为唯一，但在选择数据时也将一起使用。鉴于此，我是否只需要定义一个UNIQUE INDEX，或者除了UNIQUE INDEX之外我还应该指定一个INDEX吗？

On one of my PostgreSQL tables, I have a set of two fields that will be defined as being unique in the table, but will also both be used together when selecting data. Given this, do I only need to define a UNIQUE INDEX, or should I specify an INDEX in addition to the UNIQUE INDEX?

这个？

CREATE UNIQUE INDEX mytable_col1_col2_idx ON mytable (col1, col2);

还是这个？

CREATE UNIQUE INDEX mytable_col1_col2_uidx ON mytable (col1, col2);
CREATE INDEX mytable_col1_col2_idx ON mytable (col1, col2);

推荐答案

如果你有一个独特的指数那么你就没有还需要INDEX - 这将是多余的。 UNIQUE INDEX既是唯一约束，也可以像任何其他索引一样使用索引。

If you have a UNIQUE INDEX then you don't also need the INDEX - it would be redundant. A UNIQUE INDEX is both a unique constraint and an index that can be used like any other index.

来自文档：

注意：向表中添加唯一约束的首选方法是ALTER TABLE ... ADD CONSTRAINT。使用索引来强制执行唯一约束可以被视为不应直接访问的实现细节。但是，应该知道不需要在唯一列上手动创建索引;这样做只会复制自动创建的索引。

Note: 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.

强调我的。

这篇关于我应该同时指定INDEX和UNIQUE INDEX吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！