建約束(Constraint)時隱式建立索引(Index)及先建立索引後建立約束的區別

關於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

The information in this document applies to:

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

Symptoms

Drop constraint does not drop a associated index in 10G

Cause

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.

Solution

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

References

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

Keywords

USING~INDEX;