SQL> create table t_using(a int constraint con_not_null not null,b char(10)
2 constraint con_uni unique,c int constraint con_check check(c>10));

Table created.

SQL> select index_name,index_type,table_name,uniqueness,compression,status from user_indexes where table_name='T_USING';

INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
TABLE_NAME                     UNIQUENES COMPRESS STATUS
------------------------------ --------- -------- --------
CON_UNI                        NORMAL
T_USING                        UNIQUE    DISABLED VALID

SQL> select constraint_name,constraint_type,table_name,status,index_name from user_constraints where constraint_name='PK_T_USING';

no rows selected

SQL> select constraint_name,constraint_type,table_name,status,index_name from user_constraints where table_name='T_USING';

CONSTRAINT_NAME C TABLE_NAME STATUS
------------------------------ - ------------------------------ --------
INDEX_NAME
------------------------------
CON_NOT_NULL C T_USING ENABLED

CON_CHECK C T_USING ENABLED

CON_UNI U T_USING ENABLED
CON_UNI

SQL> drop index con_uni;#删除不了此索引，因为此索引是由唯一约束生成的，可以直接删除对应的约束
drop index con_uni
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL> drop constraint con_uni;

ITPub博客

oracle10.2.0.4_create table_constraint_index

最新文章