oracle的约束隐式创建索引和先索引后约束的区别

oracle的约束隐式创建索引和先索引后约束的区别

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

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

参考metalink文档：309821.1

实验验证下
$ ss

SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 1 18:29:31 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 889389056 bytes
Fixed Size 2233480 bytes
Variable Size 830475128 bytes
Database Buffers 50331648 bytes
Redo Buffers 6348800 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> conn hr/hr
Connected.

先创建的索引，无论是否是unique索引，都不会随约束删除而被删除
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

SQL> drop index IND ;

Index dropped.

SQL> create unique index ind2 on test ( a );

Index created.

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

Table altered.

SQL> alter table test drop constraint c2_pk;

Table altered.

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

INDEX_NAME
------------------------------
IND2

清理一下环境，删除索引，然后直接建约束，隐式创建索引，索引会因为约束被删除，而同时被删除
SQL> drop index ind2 ;

Index dropped.

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

Table altered.

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

INDEX_NAME
------------------------------
C2_PK

SQL> alter table test drop constraint c2_pk;

Table altered.

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

no rows selected