主键、唯一键约束、唯一索引区别之创建
主键、唯一键约束、唯一索引区别之删除
一:查看一下字典表中,约束的情况
SCOTT@ORA11GR2>select constraint_name,constraint_type,table_name,status from user_constraints where table_name=upper('student');
CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME STATUS
-------------------- --------------- --------------- --------
PK_STUDENT_SNO P STUDENT ENABLED
UK_STUDENT_SNAME U STUDENT ENABLED
SCOTT@ORA11GR2>
二:查看一下字典表中,索引的情况
SCOTT@ORA11GR2>select table_name,column_name,index_name from user_ind_columns where table_name=upper('student');
TABLE_NAME COLUMN_NAME INDEX_NAME
--------------- --------------- --------------------
STUDENT SNO PK_STUDENT_SNO
STUDENT SNAME UK_STUDENT_SNAME
STUDENT IDCARD UIDX_STUDENT_IDCARD
SCOTT@ORA11GR2>
SCOTT@ORA11GR2>select index_name,uniqueness,tablespace_name,status from user_indexes where table_name=upper('student');
INDEX_NAME UNIQUENES TABLESPACE_NAME STATUS
-------------------- --------- --------------- --------
UIDX_STUDENT_IDCARD UNIQUE USERS VALID
UK_STUDENT_SNAME UNIQUE USERS VALID
PK_STUDENT_SNO UNIQUE USERS VALID
SCOTT@ORA11GR2>
SCOTT@ORA11GR2>select constraint_name,constraint_type,table_name,status from user_constraints where table_name=upper('student');
CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME STATUS
-------------------- --------------- --------------- --------
PK_STUDENT_SNO P STUDENT ENABLED
UK_STUDENT_SNAME U STUDENT ENABLED
SCOTT@ORA11GR2>
三:将主键、唯一键、唯一索引diable
SCOTT@ORA11GR2>
alter table student disable constraint uk_student_sname;
Table altered.
SCOTT@ORA11GR2>alter table student disable constraint pk_student_sno;
Table altered.
SCOTT@ORA11GR2>alter index uidx_student_idcard disable;
alter index uidx_student_idcard disable
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
SCOTT@ORA11GR2>
将索引disable报错,官方文档有这么一句话DISABLE applies only to a function-based index. 也就是说,disable只适用基于函数的索引,所以,想让索引失效需要使用unusable,如下:
alter index uidx_student_idcard unusable;
Index altered.
SCOTT@ORA11GR2>
四:再次查看字典表中关于约束的相关信息
两个主键和唯一键都编程disable状态
SCOTT@ORA11GR2>select constraint_name,constraint_type,table_name,status from user_constraints where table_name=upper('student');
CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME STATUS
-------------------- --------------- --------------- --------
PK_STUDENT_SNO P STUDENT DISABLED
UK_STUDENT_SNAME U STUDENT DISABLED
SCOTT@ORA11GR2>
五:再次查看字典表中关于索引的相关信息
发现,之前两个约束创建的索引已消失,唯一索引的状态也变为失效状态
SCOTT@ORA11GR2>select table_name,column_name,index_name from user_ind_columns where table_name=upper('student');
TABLE_NAME COLUMN_NAME INDEX_NAME
--------------- --------------- --------------------
STUDENT IDCARD UIDX_STUDENT_IDCARD
SCOTT@ORA11GR2>select index_name,uniqueness,tablespace_name,status from user_indexes where table_name=upper('student');
INDEX_NAME UNIQUENES TABLESPACE_NAME STATUS
-------------------- --------- --------------- --------
UIDX_STUDENT_IDCARD UNIQUE USERS UNUSABLE
SCOTT@ORA11GR2>
六:使约束及索引生效
注:索引需要rebuild的方式使其生效
SCOTT@ORA11GR2>alter table student enable constraint uk_student_sname;
Table altered.
SCOTT@ORA11GR2>alter table student enable constraint pk_student_sno;
Table altered.
SCOTT@ORA11GR2>alter index uidx_student_idcard rebuild;
Index altered.
SCOTT@ORA11GR2>
七:最后再查看字典表中关于约束的相关信息
SCOTT@ORA11GR2>select constraint_name,constraint_type,table_name,status from user_constraints where table_name=upper('student');
CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME STATUS
-------------------- --------------- --------------- --------
PK_STUDENT_SNO P STUDENT ENABLED
UK_STUDENT_SNAME U STUDENT ENABLED
SCOTT@ORA11GR2>
八:最后再查看字典表中关于索引的相关信息
SCOTT@ORA11GR2>select table_name,column_name,index_name from user_ind_columns where table_name=upper('student');
TABLE_NAME COLUMN_NAME INDEX_NAME
--------------- --------------- --------------------
STUDENT SNAME UK_STUDENT_SNAME
STUDENT SNO PK_STUDENT_SNO
STUDENT IDCARD UIDX_STUDENT_IDCARD
SCOTT@ORA11GR2>
SCOTT@ORA11GR2>select index_name,uniqueness,tablespace_name,status from user_indexes where table_name=upper('student');
INDEX_NAME UNIQUENES TABLESPACE_NAME STATUS
-------------------- --------- --------------- --------
UIDX_STUDENT_IDCARD UNIQUE USERS VALID
PK_STUDENT_SNO UNIQUE USERS VALID
UK_STUDENT_SNAME UNIQUE USERS VALID
SCOTT@ORA11GR2>
小结:
1)将主键约束唯一键约束diable的时候,那么相应的索引也将自动删除;
2)将唯一索引unusable以后,索引还在,只是将其不可用,使其可用,使用rebuild;
3)主键和唯一键约束enable以后,相应的索引也将自动创建。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/685769/viewspace-743328/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/685769/viewspace-743328/