唯一性索引和唯一性约束

lovegigi1999

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.