Oracle 唯一索引
今天我在 10g 中创建一个新表时，我注意到一个有趣的行为。这是我所做的一个示例：
CREATE TABLE test_table ( field_1 INTEGER PRIMARY KEY );
默认情况下，Oracle 将为主键创建一个非空唯一索引。我仔细检查了这一点。快速检查后，我发现了一个唯一的索引名称 SYS_C0065645。到目前为止，一切都按预期进行。现在我这样做了：
CREATE TABLE test_table ( field_1 INTEGER,
CONSTRAINT pk_test_table PRIMARY KEY (field_1) USING INDEX (CREATE INDEX idx_test_table_00 ON test_table (field_1)));
在描述我新创建的索引 idx_test_table_00 后，我发现它不是唯一的。我尝试向表中插入重复数据，被主键约束阻止，证明功能没有受到影响。对我来说，Oracle 允许将非唯一索引用于主键约束似乎很奇怪。为什么这是允许的？
I was creating a new table today in 10g when I noticed an interesting behavior. Here is an example of what I did:
CREATE TABLE test_table ( field_1 INTEGER PRIMARY KEY );
Oracle will by default, create a non-null unique index for the primary key. I double checked this. After a quick check, I find a unique index name SYS_C0065645. Everything is working as expected so far. Now I did this:
CREATE TABLE test_table ( field_1 INTEGER,
CONSTRAINT pk_test_table PRIMARY KEY (field_1) USING INDEX (CREATE INDEX idx_test_table_00 ON test_table (field_1)));
After describing my newly created index idx_test_table_00, I see that it is non-unique. I tried to insert duplicate data into the table and was stopped by the primary key constraint, proving that the functionality has not been affected. It seems strange to me that Oracle would allow a non-unique index to be used for a primary key constraint. Why is this allowed?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
唯一索引和非唯一索引实际上没有结构上的区别，Oracle 可以使用其中任何一个作为 PK 约束。允许像这样的 PK 定义的一个优点是您可以禁用或推迟数据加载的约束 - 这对于唯一索引来说是不可能的，因此有人可能会认为这种实现更灵活。
There is actually no structural difference between a unique index and a non-unique index, Oracle can use either for the PK constraint. One advantage of allowing a PK definition like this is that you can disable or defer the constraint for data loading - this isn't possible with a unique index, so one could argue that this implementation is more flexible.
为什么不允许呢？我喜欢 Oracle 为您提供的大量选择和灵活性。
也许您可以创建一个索引并将其用于两个目的：
哦，而且索引与not null方面无关。
Why not allow it? I love that Oracle gives you lots of options and flexibility.
Maybe you can create one index and use it for two purposes:
Oh, and the index has nothing to do with the not null aspect.
请参阅这个优秀的Richard Foote 撰写的关于非唯一索引监管主键的文章。 Richard 表明，使用非唯一索引时性能会受到影响。
换句话说：不要使用非唯一索引来监管主键约束，除非您确实需要可延迟约束。
see this excellent article about non-unique indexes policing primary keys by Richard Foote. Richard shows that you will take a performance hit when using a non-unique index.
In other words: don't use non-unique indexes to police a primary key constraint unless you really need the constraint to be deferrable.