ITPub博客

首页 > 数据库 > 数据库开发技术 > constraint和index的一点关系!

constraint和index的一点关系!

原创 数据库开发技术 作者:warehouse 时间:2008-04-23 23:42:03 0 删除 编辑

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>

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

请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    717
  • 访问量
    5115400