在Rails中，两列上的单独索引和对上的唯一约束

Question

我的应用程序使用PostgreSQL数据库。我有一个看起来像这样的迁移：

class CreateTagAssignments < ActiveRecord::Migration
def change
create_table :tag_assignments do |t|
t.integer :tag_id
t.integer :quote_id
t.integer :user_id
t.timestamps
end
add_index :tag_assignments, :tag_id
add_index :tag_assignments, :quote_id
end
end

这两列会频繁地搜索记录，所以我想为每一列都有一个单独的索引。但现在我想在数据库级别强制执行对(tag_id，quote_id)的唯一性。我尝试了add_index :tag_assignments, [:tag_id, :quote_id], unique: true，但我得到了错误：

PG::Error: ERROR:  could not create unique index "index_tag_assignments_on_tag_id_and_quote_id"
DETAIL:  Key (tag_id, quote_id)=(10, 1) is duplicated.
: CREATE UNIQUE INDEX "index_tag_assignments_on_tag_id_and_quote_id" ON "tag_assignments" ("tag_id", "quote_id")

那么，多个索引显然完成了多列索引的工作？如果是这样的话，那么我可以用ALTER TABLE ... ADD CONSTRAINT添加约束，但如何在ActiveRecord中添加呢？

编辑：手动执行ALTER TABLE ... ADD CONSTRAINT会产生相同的错误。

Answer 1

正如Erwin所指出的，"Key(tag_id，quote_id)=(10，1)is duplicated"约束违反错误消息告诉您的唯一约束已经被现有数据违反。我从您的模型中可以看出，不同的用户可以在标记和引号之间引入一个共同的关联，因此当您试图仅约束quote_id和tag_id对的唯一性时，您会看到重复。复合索引对于前导键上的索引访问仍然很有用(尽管效率略低于单列索引，因为复合索引的键密度较低)。您可能会获得所需的速度，以及带有两个索引的适当的唯一约束，其中一个id上有单列索引，所有三个id上都有复合索引，另一个id作为其前导字段。如果从标签到报价的映射比从报价到标签的映射更频繁，我会尝试这样做：

add_index :tag_assignments, :tag_id
add_index :tag_assignments, [:quote_id,:tag_id,:user_id], unique: true

如果您使用的是Pg>=9.2，您可以利用9.2的索引可见性图来启用覆盖索引的仅索引扫描。在这种情况下，使上面的第一个索引包含所有三个id可能会有好处，其中tag_id和quote_id领先：

add_index :tag_assignments, [:tag_id,:quote_id,user_id]

目前还不清楚user_id是如何约束查询的，所以您可能会发现，您希望索引的位置也提前提升。

Answer 2

那么多个索引显然可以完成多列索引的工作？

根据你的描述，这个结论是不真实的，也是没有根据的。错误消息表明情况正好相反。多列索引或多列上的UNIQUE约束(也实现多列索引)提供了无法从多个单列索引中获得的功能。