建约束(Constraint)时隐式创建索引(Index)及先创建索引后建立约束的区别

最新推荐文章于 2025-07-27 12:37:32 发布

转载最新推荐文章于 2025-07-27 12:37:32 发布 · 627 阅读

文章标签：

探讨了Oracle 9i与10g版本中，删除约束时索引的处理方式差异。在Oracle 10g中，不论索引是否唯一，删除约束时都不会删除用户创建的索引。

关于USING INDEX

两种情况：
1.对于创建约束时隐式创建的索引，在做删除操作的时候: 9i~10g都会连带删除该索引

2.对于先创建索引，再创建约束（使用到此索引）这种情况:
9i版本：需要区分索引是否唯一：
如果索引是唯一的，则删除约束的时候，会连带删除索引；如果非唯一的，则不会删除索引。
10g版本：无论索引是否唯一，都只是删除约束，索引不会删除。
可以参考metalink文档：309821.1

主题:	Oracle 10G Does not Drop User Index Associated With Unique/Primary Key Constraints
	文档 ID:	309821.1	类型:	PROBLEM
	Modified Date:	18-MAY-2007	状态:	MODERATED

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.0
This problem can occur on any platform.

Drop constraint does not drop a associated index in 10G

In 10g, a change was made to an internal function "atbdui" to not to drop the user index when the constraint using the index is dropped.

In 9i, this behavior. is different the user index
gets dropped when the constraint is dropped.

Select index_name,generated from dba_indexes where index_name='< ;index_name >';

Generated column would show 'Y' If the index is system generated.If 'N' ,it is user generated.

This behaviour is because of the code changes made in 10G.

Use the workaround:

alter table < table > drop constraint < constraint > drop index;

Note

If the index is non unique, we can still use them for enforcing primary key constraints or unique constraints.

But dropping the constraint does not drop the non-unique index.
This behaviour is seen from 8174 to 10.2.0.0.

Sample Output

SQL> create table test( a number );

Table created.

SQL> create index ind on test ( a );

Index created.

SQL> alter table test add constraint c1_pk primary key(a) using index;

Table altered.

SQL> select index_name from user_indexes where table_name='TEST';

INDEX_NAME
------------------------------
IND

SQL> alter table test drop constraint c1_pk;

Table altered.

SQL> select index_name from user_indexes where table_name='TEST';

INDEX_NAME
------------------------------
IND

Note 292096.1 - Encountering ora-00955 when trying to create primary key on an imported table in 10g

Keywords

USING~INDEX;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-617710/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-617710/