Oracle 唯一约束和唯一索引

Oracle unique constraint and unique index

提问by a1ex07

Could someone clarify what is the purpose of having unique index without unique constraint (Oracle)? For example,

有人能澄清一下没有唯一约束（Oracle）的唯一索引的目的是什么？例如，

create table test22(id int, id1 int, tmp varchar(20));
create unique index idx_test22 on test22(id);
insert into test22(id, id1, tmp) values (1, 2, 'aaa'); // ok
insert into test22(id, id1, tmp) values (1, 2, 'aaa'); // fails, ORA-00001: unique   
  // constraint (TEST.IDX_TEST22) violated

So far it looks like there is a constraint. But

到目前为止，似乎有一个限制。但

create table test33(id int not null primary key, 
test22_id int not null, 
foreign key(test22_id) references test22(id) );

also fails with "ORA-02270: no matching unique or primary key for this column-list". I'm totally confused by this behaviour. Is there a constraint or not?

也失败了"ORA-02270: no matching unique or primary key for this column-list"。我完全被这种行为搞糊涂了。有没有限制？

There are many articles that explain why it's possible to have a unique constraint without unique index; that is clear and makes perfect sense. However, I don't understand the reason for unique index without constraint.

有很多文章解释了为什么可以有一个没有唯一索引的唯一约束；这是明确的，完全有道理的。但是，我不明白没有约束的唯一索引的原因。

回答by Justin Cave

A constraint and an index are separate logical entities. A unique constraint, for example, is visible in USER_CONSTRAINTS(or ALL_CONSTRAINTSor DBA_CONSTRAINTS). An index is visible in USER_INDEXES(or ALL_INDEXESor DBA_INDEXES).

约束和索引是独立的逻辑实体。例如，唯一约束在USER_CONSTRAINTS(orALL_CONSTRAINTS或DBA_CONSTRAINTS) 中可见。索引在USER_INDEXES(orALL_INDEXES或DBA_INDEXES) 中可见。

A unique constraint is enforced by an index though it is possible (and sometimes necessary) to enforce a unique constraint using a non-unique index. A deferrable unique constraint, for example, is enforced using a non-unique index. If you create a non-unique index on a column and subsequently create a unique constraint, you can also use that non-unique index to enforce the unique constraint.

唯一约束由索引强制执行，尽管使用非唯一索引强制执行唯一约束是可能的（有时是必要的）。例如，可延迟唯一约束是使用非唯一索引强制执行的。如果在列上创建非唯一索引并随后创建唯一约束，则还可以使用该非唯一索引来强制执行唯一约束。

In practice, a unique index acts very much like a unique, non-deferrable constraint in that it raises the same error that a unique constraint raises since the implementation of unique constraints uses the index. But it is not quite the same because there is no constraint. So, as you've seen, there is no unique constraint so you cannot create a foreign key constraint that references the column.

实际上，唯一索引的行为非常类似于唯一的、不可延迟的约束，因为它引发的错误与唯一约束引发的错误相同，因为唯一约束的实现使用了索引。但它并不完全相同，因为没有约束。因此，如您所见，没有唯一约束，因此您无法创建引用该列的外键约束。

There are cases where you can create a unique index that you cannot create a unique constraint. A function-based index, for example, that enforces conditional uniqueness. If I wanted to create a table that supported logical deletes but ensure that COL1is unique for all non-deleted rows

在某些情况下，您可以创建唯一索引，而不能创建唯一约束。例如，强制条件唯一性的基于函数的索引。如果我想创建一个支持逻辑删除的表，但要确保COL1它对所有未删除的行都是唯一的

SQL> ed
Wrote file afiedt.buf

  1  CREATE TABLE t (
  2    col1 number,
  3    deleted_flag varchar2(1) check( deleted_flag in ('Y','N') )
  4* )
SQL> /

Table created.

SQL> create unique index idx_non_deleted
  2      on t( case when deleted_flag = 'N' then col1 else null end);

Index created.

SQL> insert into t values( 1, 'N' );

1 row created.

SQL> insert into t values( 1, 'N' );
insert into t values( 1, 'N' )
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.IDX_NON_DELETED) violated


SQL> insert into t values( 1, 'Y' );

1 row created.

SQL> insert into t values( 1, 'Y' );

1 row created.

But if we're talking about a straight unique non-function based index, there are probably relatively few cases where it really makes more sense to create the index rather than creating the constraint. On the other hand, there are relatively few cases where it makes much difference in practice. You'd almost never want to declare a foreign key constraint that referenced a unique constraint rather than a primary key constraint so you rarely lose something by only creating the index and not creating the constraint.

但是，如果我们谈论的是基于非函数的直接唯一索引，那么创建索引而不是创建约束确实更有意义的情况可能相对较少。另一方面，它在实践中产生很大差异的情况相对较少。您几乎从不想声明引用唯一约束而不是主键约束的外键约束，因此您很少会因为只创建索引而不创建约束而丢失某些东西。

回答by Syamjith

Another point which may be useful in this context is : Disabling/Dropping an existing unique constraint do not drop the underlying unique index. You got to drop the unique index explicitly.

在此上下文中可能有用的另一点是：禁用/删除现有唯一约束不会删除基础唯一索引。您必须明确删除唯一索引。

回答by biddut

You can not make conditional uniqueness by declaring a unique constraint, But you can do it by declaring a unique index.

您不能通过声明唯一约束来实现条件唯一性，但可以通过声明唯一索引来实现。

Supporse if you try to execute below:

支持如果您尝试在下面执行：

alter table test22 
add constraint test22_u 
unique (id, case when tmp = 'aaa' then null else tmp end);

ORA-00904: : invalid identifier 

But if you can do it by using the unique index

但是如果你可以通过使用唯一索引来做到这一点

create unique index test22_u 
on test22 ( customer_id, 
case when is_default = 'Y' then null else address_id end)

回答by Roger Smith

As was already explained in other answers: constraints and the indexes are different entities. But they lack precise definitions and official comments on the topic. Before we discuss the relationship between these two entities lets take a look at their purpose independent of each other.

正如其他答案中已经解释的那样：约束和索引是不同的实体。但他们缺乏对该主题的准确定义和官方评论。在我们讨论这两个实体之间的关系之前，让我们先看看它们彼此独立的目的。

Purpose of a constraint¹:

约束^{1 的}目的：

Use a constraint to define an integrityconstraint-- a rule that restricts the valuesin a database.

使用约束来定义完整性约束——一种限制数据库中值的规则。

The purposes of an index²:

索引²的目的：

You can create indexes on columns to speed up queries. Indexes provide faster accessto data for operations that return a small portion of a table's rows.

In general, you should create an index on a column in any of the following situations:

• The column is queried frequently.
• A referential integrity constraint exists on the column.
• A UNIQUE key integrity constraint exists on the column.

您可以在列上创建索引以加快查询速度。索引为返回表行的一小部分的操作提供了对数据的更快访问。

通常，您应该在以下任何一种情况下对列创建索引：

• 该列经常被查询。
• 列上存在参照完整性约束。
• 列上存在唯一键完整性约束。

Now we know what constraints and indexes are, but what is the relationship between them?

现在我们知道约束和索引是什么，但它们之间的关系是什么？

The relationship between indexes and constraints is³:

索引和约束之间的关系是³：

• a constraint MIGHT create an index or use an existing index to efficient enforce itself. For example, a PRIMARY KEY constraint will either create an index (unique or non-unique depending) or it will find an existing suitable index and use it.

• an index has nothing to do with a constraint. An index is an index.

So, a constraint MIGHT create/use and index. An INDEX is an INDEX, nothing more, nothing less.

• 约束可能会创建索引或使用现有索引来有效地强制执行自身。例如，PRIMARY KEY 约束将创建一个索引（唯一或非唯一依赖），或者它将找到一个现有的合适索引并使用它。

• 索引与约束无关。索引是一个索引。

因此，约束可能会创建/使用和索引。一个 INDEX 就是一个 INDEX，仅此而已。

So sum this up and directly address the following sentence from your question:

所以总结一下，直接从你的问题中解决以下句子：

However, I don't understand the reason for unique index without constraint.

但是，我不明白没有约束的唯一索引的原因。

Indexes speed up queries and integrity checks (constraints). Also for conditional uniqueness a unique (functional) index is used as this cannot be achieved with a constraint.

索引加速查询和完整性检查（约束）。同样对于条件唯一性，使用唯一（功能）索引，因为这无法通过约束实现。

Hopefully this brings a little bit more clarification to the whole topic, but there is one aspect of the original question that remains unanswered:

希望这能给整个主题带来更多的澄清，但原始问题的一个方面仍未得到解答：

Why did the following error occur when no constraint existed:

不存在约束时为什么会出现以下错误：

ORA-00001: unique constraint (TEST.IDX_TEST22) violated

ORA-00001: 违反了唯一约束 (TEST.IDX_TEST22)

The answer is simple: there is no constraintand the error message misnamesit!

答案很简单：没有约束，而且错误消息命名错误！

See the official "Oracle Ask TOM" comment ⁴on the same problem:

同问题见官方“Oracle Ask TOM”评论⁴：

It isn't a constraint. the error message "misnames" it.
If it were a constraint, you could create a foreign key to it -- but you cannot.

这不是约束。错误消息“错误地命名”了它。
如果它是一个约束，你可以为它创建一个外键——但你不能。

Hope it helps.

希望能帮助到你。

Links:

链接：

¹Oracle 10g Documentation on Constraints

¹ Oracle 10g 约束文档

²Oracle 10g Documentation on Selecting an Index Strategy

² Oracle 10g 关于选择索引策略的文档

³⁴"Oracle Ask TOM" answer to a similar problem

^{3 4} “Oracle Ask TOM”对类似问题的回答