sql - 如何使用更长的唯一索引创建主键？

Question

创建主键时可以指定索引:

CREATE TABLE t (a NUMBER, b NUMBER);
ALTER TABLE t ADD PRIMARY KEY (a,b) USING INDEX (CREATE INDEX i ON t(a,b));

这也适用于列子集:

ALTER TABLE t ADD PRIMARY KEY (a) USING INDEX (CREATE INDEX i ON t(a,b));

我更喜欢唯一索引(因为非唯一索引将 rowid 添加到每个键，这使得索引更大但速度稍慢)。这也有效:

ALTER TABLE t ADD PRIMARY KEY (a,b) USING INDEX (CREATE UNIQUE INDEX i ON t(a,b));

但是，子集 和 唯一索引会导致错误:

ALTER TABLE t ADD PRIMARY KEY (a) USING INDEX (CREATE UNIQUE INDEX u ON t(a,b));
ORA-14196: Specified index cannot be used to enforce the constraint.

总结:

OK     PRIMARY KEY (a,b) USING INDEX (        INDEX(a,b) )
OK     PRIMARY KEY (a,b) USING INDEX ( UNIQUE INDEX(a,b) )
OK     PRIMARY KEY (a)   USING INDEX (        INDEX(a,b) )
ERROR  PRIMARY KEY (a)   USING INDEX ( UNIQUE INDEX(a,b) )

我完全不明白为什么那是不可能的。

我经常需要它，例如对于具有两个主键列(比如国家、城市)和另外一个列(比如人口)的表。因为我总是查询更多的列，所以三列索引是有意义的。由于前两列是唯一的(每个主键)，索引也将是唯一的，但 Oracle 不允许我这样做。为什么？

Answer 1

这是一条不适合放在评论区的评论，它可能是明显错误的。

我的看法是，Oracle 可以使用 1) 唯一索引或 2) 非唯一索引(由于历史原因可能存在的功能异常)来强制主键的唯一性。因此:

• 案例 #1:如果它使用唯一索引，则查找值是否唯一的所有繁重工作都由索引本身完成。这是其功能的一部分。

• 案例 #2:如果它使用非唯一索引，索引用于存储值，但唯一性由主键约束本身验证，主键约束本身扫描索引以获取多个值。

现在，您的四个示例分为:

• 案例 #1(非唯一)
• 案例 #2(唯一)
• 案例 #1(非唯一)
• 不是案例 #1，也不是案例 #2。这就是我认为 Oracle 不允许这样做的原因。

当然，如果有人知道，请指正。