constraint和index的一點關係！

primary key和unique約束是要依賴index的，下面透過試驗來看看他們之間的依賴關係!

[@more@]

SQL> select * from tt;

ID NA
---------- --
1 a
2 b
3 c
4 d
5 e
6 f
1000 h

已選擇7行。
SQL> alter table tt add constraint pk_tt primary key (id);

表已更改。
SQL> select index_name,table_name,uniqueness,status from user_indexes where tabl
e_name='TT';

INDEX_NAME TABLE_NAME UNIQUENES STATUS
------------------------------ ------------------------------ --------- --------

PK_TT TT UNIQUE VALID

SQL> alter table tt add constraint uni_tt unique (name);

表已更改。
SQL> select index_name,table_name,uniqueness,status from user_indexes where tabl
e_name='TT';

INDEX_NAME TABLE_NAME UNIQUENES STATUS
------------------------------ ------------------------------ --------- --------

PK_TT TT UNIQUE VALID
UNI_TT TT UNIQUE VALID

/*我們都知道在建立primary key和unique約束的時候系統自動會建立unique index，
上面的測試也驗證了這一點。不過透過這種方式建立的index存在一點問題那就是
當constraint被disable的時候，index被drop了！而當constraint被enable時index
又被重新建立，如果在一個高可用的系統的一個大表上做這樣的操作時要慎重，因為
建立index是要產生lock的。*/
SQL> alter table tt disable constraint pk_tt;

表已更改。

SQL> alter table tt disable constraint uni_tt;

表已更改。

SQL> select index_name,table_name,uniqueness,status from user_indexes where tabl
e_name='TT';

未選定行

SQL> alter table tt enable constraint pk_tt;

表已更改。

SQL> alter table tt enable constraint uni_tt;

表已更改。

SQL> select index_name,table_name,uniqueness,status from user_indexes where tabl
e_name='TT';

INDEX_NAME TABLE_NAME UNIQUENES STATUS
------------------------------ ------------------------------ --------- --------

PK_TT TT UNIQUE VALID
UNI_TT TT UNIQUE VALID
--當約束（primary key 和unique）被刪除時，伴隨著約束的建立而被建立的index也隨之被刪除！
SQL> alter table tt drop constraint pk_tt;

表已更改。

SQL> alter table tt drop constraint uni_tt;

表已更改。

SQL> select index_name,table_name,uniqueness,status from user_indexes where tabl
e_name='TT';

未選定行
/*如果事先在要建立約束(primary key 和unique)的列上存在非唯一index，那麼建立約束時oracle會自動使用該index而不會重新建立;以這種方式存在的index的好處是約束被disable之後index依然存在並且處於valid狀態或者說當約束由disable變成enable時index不需要被重建；如果事先存在唯一index，那麼在這樣的列上建立constraint時儘管也可以使用該唯一index，但是當把constraint變成disable狀態時index還是被刪除了，也就是說當再次enable 約束時index還是需要重新建立的。所以把constraint和非唯一index結合起來何嘗不是一種好的做法。這樣如果使用非唯一index，那麼在一個大表上把約束從enable狀態變成disable狀態可以大大降低時間，因為不需要重建index；另外的好處就是當constraint被drop之後index依然存在而且有效*/
SQL> create index idx_tt_id on tt(id);

索引已建立。

SQL> create index idx_tt_name on tt(name);

索引已建立。

SQL> alter table tt add constraint pk_tt primary key (id);

表已更改。

SQL> alter table tt add constraint uni_tt unique (name) using index idx_tt_name;

表已更改。

SQL> select index_name,table_name,uniqueness,status from user_indexes where tabl
e_name='TT';

INDEX_NAME TABLE_NAME UNIQUENES STATUS
------------------------------ ------------------------------ --------- --------

IDX_TT_ID TT NONUNIQUE VALID
IDX_TT_NAME TT NONUNIQUE VALID

SQL> alter table tt disable constraint pk_tt;

表已更改。

SQL> alter table tt disable constraint uni_tt;

表已更改。

SQL> select index_name,table_name,uniqueness,status from user_indexes where tabl
e_name='TT';

INDEX_NAME TABLE_NAME UNIQUENES STATUS
------------------------------ ------------------------------ --------- --------

IDX_TT_ID TT NONUNIQUE VALID
IDX_TT_NAME TT NONUNIQUE VALID

SQL> alter table tt enable constraint pk_tt;

表已更改。

SQL> alter table tt enable constraint uni_tt;

表已更改。

SQL> select index_name,table_name,uniqueness,status from user_indexes where tabl
e_name='TT';

INDEX_NAME TABLE_NAME UNIQUENES STATUS
------------------------------ ------------------------------ --------- --------

IDX_TT_ID TT NONUNIQUE VALID
IDX_TT_NAME TT NONUNIQUE VALID
--index一旦被約束所依賴，那麼該index將不能被刪除
SQL> drop index idx_tt_id ;
drop index idx_tt_id
*
第 1 行出現錯誤:
ORA-02429: 無法刪除用於強制唯一/主鍵的索引

SQL> drop index idx_tt_name;
drop index idx_tt_name
*
第 1 行出現錯誤:
ORA-02429: 無法刪除用於強制唯一/主鍵的索引

SQL> alter table tt drop constraint pk_tt;

表已更改。

SQL> alter table tt drop constraint uni_tt;

表已更改。

SQL> select index_name,table_name,uniqueness,status from user_indexes where tabl
e_name='TT';

INDEX_NAME TABLE_NAME UNIQUENES STATUS
------------------------------ ------------------------------ --------- --------

IDX_TT_ID TT NONUNIQUE VALID
IDX_TT_NAME TT NONUNIQUE VALID

SQL> drop index idx_tt_id ;

索引已刪除。

SQL> drop index idx_tt_name;

索引已刪除。

SQL> select index_name,table_name,uniqueness,status from user_indexes where tabl
e_name='TT';

未選定行
--再來看看複合index的情況：當在前導列上建立約束(primary key and unique)時，複合索引
依然可以被primary key所依賴而不許建立uniqie index，但是當不在index的前導列上建立
約束時此時存在的複合index就不能被約束所依賴！
SQL> create index idx_tt_id_name on tt(id , name);

索引已建立。

SQL> alter table tt add constraint pk_tt primary key (id);

表已更改。

SQL> alter table tt add constraint uni_tt unique (name) ;

表已更改。

SQL> select index_name,table_name,uniqueness,status from user_indexes where tabl
e_name='TT';

INDEX_NAME TABLE_NAME UNIQUENES STATUS
------------------------------ ------------------------------ --------- --------

IDX_TT_ID_NAME TT NONUNIQUE VALID
UNI_TT TT UNIQUE VALID

SQL> alter table tt drop constraint uni_tt;

表已更改。

SQL> alter table tt add constraint uni_tt unique (name) using index idx_tt_id_na
me;
alter table tt add constraint uni_tt unique (name) using index idx_tt_id_name
*
第 1 行出現錯誤:
ORA-14196: 指定的索引不能用於強制約束條件。

SQL>