create table t(id int primary key);
select object_name,object_type from user_objects;
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
SYS_C0010843 INDEX
T TABLE
SQL> alter table t disable constraint SYS_C0010843 keep index; 默认是drop index
Table altered.
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
SYS_C0010843 INDEX
T TABLE
SQL> insert into t values(1);
1 row created.
SQL> insert into t values(1);
insert into t values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (ZHOUYF.SYS_C0010843) violated 默认是validate
SQL> alter table t disable novalidate constraint SYS_C0010843 keep index;
Table altered.
SQL> insert into t values(1);
insert into t values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (ZHOUYF.SYS_C0010843) violated
select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS,DEFERRABLE,DEFERRED,VALIDATED,INVALID from user_constraints;
SQL> /
CONSTRAINT_NAME C TABLE_NAME STATUS DEFERRABLE DEFERRED VALIDATED INVALID
------------------------------ - ------------------------------ -------- -------------- --------- ------------- -------
BIN$99TD8B13d0DgQAB/AQAY3w==$0 P BIN$99TD8B14d0DgQAB/AQAY3w==$0 DISABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED
SYS_C0010843 P T DISABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED
SQL>
后面没有继续测试了，我记得可以设置DEFERRED，这样就可以插入重复值而不检查是否唯一的。
本文出自 “骅骝向北越鸟归南” 博客，请务必保留此出处http://848436.blog.51cto.com/838436/1402941
oracle disable constraint 或者删除primary key 是否删除索引,布布扣,bubuko.com
oracle disable constraint 或者删除primary key 是否删除索引
原文：http://848436.blog.51cto.com/838436/1402941