我应该同时指定 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);

推荐答案

如果你有一个 UNIQUE INDEX 那么你就不需要 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屋！