唯一性索引和唯一性约束
SQL> create unique index idx_uni_empno_copy on emp_copy(empno);
Index created.
注意这里的index_type不代表是否唯一性索引,要看UNIQUENESS 字段
SQL> select index_name,status,num_rows,index_type from user_indexes where table_name='EMP_COPY';
INDEX_NAME STATUS NUM_ROWS INDEX_TYPE
------------------------------ -------- ---------- ---------------------------
IDX_UNI_EMPNO_COPY VALID 14 NORMAL
SQL> alter table emp_copy add constraint cons_uni_empname unique(ename);
Table altered.
SQL> select index_name,status,num_rows,index_type from user_indexes where table_name='EMP_COPY';
INDEX_NAME STATUS NUM_ROWS INDEX_TYPE
------------------------------ -------- ---------- ---------------------------
IDX_UNI_EMPNO_COPY UNUSABLE 14 NORMAL
CONS_UNI_EMPNAME VALID 14 NORMAL
SQL> select index_name,status,num_rows,index_type,UNIQUENESS from user_indexes where table_name='EMP_COPY';
INDEX_NAME STATUS NUM_ROWS INDEX_TYPE
------------------------------ -------- ---------- ---------------------------
UNIQUENES
---------
IDX_UNI_EMPNO_COPY VALID 14 NORMAL
UNIQUE
CONS_UNI_EMPNAME VALID 14 NORMAL
UNIQUE
唯一性约束的索引不能直接删除,要先删除约束然后再删除对应的索引
SQL> drop index CONS_UNI_EMPNAME;
drop index CONS_UNI_EMPNAME
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL> alter table emp_copy drop constraint CONS_UNI_EMPNAME;
Table altered.
SQL> select index_name,status,num_rows,index_type,UNIQUENESS from user_indexes where table_name='EMP_COPY';
INDEX_NAME STATUS NUM_ROWS INDEX_TYPE
------------------------------ -------- ---------- ---------------------------
UNIQUENES
---------
IDX_UNI_EMPNO_COPY VALID 14 NORMAL
UNIQUE
SQL> drop index IDX_UNI_EMPNO_COPY ;
Index dropped.
解决ORA-02429和ORA-02297错误 (2007-09-19 16:43:22)转载▼
标签: 杂谈 分类: MSN搬家
解决了两个错误:
ORA-02429: cannot drop index used for enforcement of unique/primary key
ORA-02297: cannot disable constraint (XXX_DJCM.PK_CONTAINER) - dependencies exist
开发的同事说有一索引已经无用,要求删除,那知道执行的时候出错:
SQL>drop index CONTAINER_I_CONTAINER_REF_NO_C;
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL>ALTER TABLE CONTAINER MODIFY PRIMARY