Oracle唯一约束和唯一索引

发布于 2024-12-06 13:32:10 字数 670 浏览 2 评论 0原文

有人可以澄清没有唯一约束（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

到目前为止，似乎存在限制。但

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

也会失败，并显示“ORA-02270：此列列表没有匹配的唯一键或主键”。我对这种行为完全感到困惑。有没有约束？

有很多文章解释了为什么可以在没有唯一索引的情况下有唯一约束；这很清楚并且完全有道理。但是，我不明白没有约束的唯一索引的原因。

原文

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

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?

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

ㄟ。诗瑗 2024-12-13 13:32:10

约束和索引是单独的逻辑实体。例如，唯一约束在 USER_CONSTRAINTS（或 ALL_CONSTRAINTS 或 DBA_CONSTRAINTS）中可见。索引在 USER_INDEXES（或 ALL_INDEXES 或 DBA_INDEXES）中可见。

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

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

在某些情况下，您可以创建唯一索引，但无法创建唯一约束。例如，基于函数的索引强制条件唯一性。如果我想创建一个支持逻辑删除的表，但确保 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.

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

A constraint and an index are separate logical entities. A unique constraint, for example, is visible in USER_CONSTRAINTS (or ALL_CONSTRAINTS or DBA_CONSTRAINTS). An index is visible in USER_INDEXES (or ALL_INDEXES or 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 COL1 is unique for all non-deleted rows

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.

回复收藏 0 原文

眸中客 2024-12-13 13:32:10

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

约束¹的目的：

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

索引²的用途：

您可以在列上创建索引以加快查询速度。对于返回一小部分表行的操作，索引可以更快地访问数据。
一般来说，在以下任一情况下，您应该在列上创建索引：
该列被频繁查询。
列上存在参照完整性约束。
该列上存在唯一的键完整性约束。

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

索引和约束的关系为³：

约束可能会创建索引或使用现有索引来有效地强制执行自身。例如，PRIMARY KEY 约束将创建一个索引（取决于唯一或非唯一），或者找到现有的合适索引并使用它。
索引与约束无关。索引就是索引。
因此，约束可能会创建/使用和索引。索引就是索引，不多不少。

因此总结一下并直接回答您问题中的以下句子：

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

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

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

为什么在不存在约束的情况下会发生以下错误：

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

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

请参阅关于同一问题的官方“Oracle Ask TOM”评论⁴：

这不是一个限制。错误消息“错误命名”了它。
如果它是一个约束，您可以为其创建一个外键 - 但您不能。

希望有帮助。

链接：

¹ Oracle 10g 文档约束

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

³⁴ “Oracle Ask TOM”回答类似问题

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¹:

Use a constraint to define an integrity constraint-- a rule that restricts the values in a database.

The purposes of an index²:

You can create indexes on columns to speed up queries. Indexes provide faster access to 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.

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

The answer is simple: there is no constraint and the error message misnames it!

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

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 Documentation on Selecting an Index Strategy

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

回复收藏 0 原文

忆沫 2024-12-13 13:32:10

在这种情况下可能有用的另一点是：
禁用/删除现有唯一约束不会删除基础唯一索引。您必须显式删除唯一索引。

回复收藏 0 原文

木有鱼丸 2024-12-13 13:32:10

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

如果您尝试执行以下操作，请支持：

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

ORA-00904: : invalid identifier

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

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

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)

回复收藏 0 原文

~没有更多了~

关于作者

潜移默化

暂无简介

文章

26 人气

关注发私信

友情链接

文江博客

Oracle唯一约束和唯一索引

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（4）

约束¹的目的：

索引²的用途：

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

为什么在不存在约束的情况下会发生以下错误：

链接：

Purpose of a constraint¹:

The purposes of an index²:

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

Why did the following error occur when no constraint existed:

Links:

关于作者

相关话题

热门标签

推荐作者

知足的幸福

我一向站在原地

慕烟庭风

秉忠贞之诚守退让之实

小兔几

mb_3y7WUgWY

友情链接

Oracle唯一约束和唯一索引

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（4）

约束1的目的：

索引2的用途：

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

为什么在不存在约束的情况下会发生以下错误：

链接：

Purpose of a constraint1:

The purposes of an index2:

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

Why did the following error occur when no constraint existed:

Links:

关于作者

相关话题

热门标签

推荐作者

知足的幸福

我一向站在原地

慕烟庭风

秉忠贞之诚 守退让之实

小兔几

mb_3y7WUgWY

友情链接

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

约束¹的目的：

索引²的用途：

Purpose of a constraint¹:

The purposes of an index²:

秉忠贞之诚守退让之实