| 9i、10g disable主键、唯一性约束的不同

9i、10g disable主键、唯一性约束的不同

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/02/9i-10g-disable-constraint-differ.html

对于主键约束与唯一性约束：
在9I中，当索引为唯一索引时，Disable constraint时，同时会把索引删除。
（注：1、索引为非唯一索引，不在本次讨论之列。通过创建一个非唯一索引，让P或U约束使用，可避免在Enable或Disable P或U约束时重建索引，同时也避免drop constraint时，删除相关的索引。
2、当Disable constraint时使用keep index选项，同样可以保留相应索引。）
而10G中，即使索引为唯一索引，在Disable Constraint时，也不会删除相应的索引。

FOR 9I：

sys@TESTDBA>select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for HPUX: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
 
sys@TESTDBA>create unique index PK_ID1_IND on test(id1);
 
Index created.
 
sys@TESTDBA>alter table test add constraint PK_ID1_CON primary key(id1) using index PK_ID1_IND;
 
Table altered.
 
sys@TESTDBA>select owner,index_name,index_type,table_name,uniqueness,status 
  2  from dba_indexes where table_name='TEST';
 
OWNER      INDEX_NAME           INDEX_TYPE      TABLE_NAME           UNIQUENES STATUS
---------- -------------------- --------------- -------------------- --------- --------
SYS        PK_ID1_IND           NORMAL          TEST                 UNIQUE    VALID
 
sys@TESTDBA>select constraint_name,constraint_type,table_name,status,index_owner,index_name 
  2  from dba_constraints where table_name='TEST' and owner='SYS';
 
CONSTRAINT_NAME      C TABLE_NAME           STATUS   INDEX_OWNER     INDEX_NAME
-------------------- - -------------------- -------- --------------- --------------------
PK_ID1_CON           P TEST                 ENABLED  SYS             PK_ID1_IND
 
sys@TESTDBA>alter table test disable constraint PK_ID1_CON;
 
Table altered.
 
--索引被删除
sys@TESTDBA>select owner,index_name,index_type,table_name,uniqueness,status 
  2  from dba_indexes where table_name='TEST';
 
no rows selected
 
sys@TESTDBA>select constraint_name,constraint_type,table_name,status,index_owner,index_name 
  2  from dba_constraints where table_name='TEST' and owner='SYS';
 
CONSTRAINT_NAME      C TABLE_NAME           STATUS   INDEX_OWNER     INDEX_NAME
-------------------- - -------------------- -------- --------------- --------------------
PK_ID1_CON           P TEST                 DISABLED

FOR 10G:

sys@ORCL>select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
 
sys@ORCL>create unique index PK_ID1_IND on test(id1);
 
Index created.
 
sys@ORCL>
sys@ORCL>alter table test add constraint PK_ID1_CON primary key(id1) using index PK_ID1_IND;
 
Table altered.
 
sys@ORCL>select owner,index_name,index_type,table_name,uniqueness,status 
  2  from dba_indexes where table_name='TEST';
 
OWNER           INDEX_NAME      INDEX_TYPE      TABLE_NAME      UNIQUENES STATUS
--------------- --------------- --------------- --------------- --------- --------
SYS             PK_ID1_IND      NORMAL          TEST            UNIQUE    VALID
 
sys@ORCL>select constraint_name,constraint_type,table_name,status,index_owner,index_name 
  2  from dba_constraints where table_name='TEST';
 
CONSTRAINT_NAME      C TABLE_NAME      STATUS   INDEX_OWNER     INDEX_NAME
-------------------- - --------------- -------- --------------- ---------------
PK_ID1_CON           P TEST            ENABLED  SYS             PK_ID1_IND
 
sys@ORCL>alter table test disable constraint PK_ID1_CON;
 
Table altered.
 
--索引未被删除
sys@ORCL>select owner,index_name,index_type,table_name,uniqueness,status 
  2  from dba_indexes where table_name='TEST';
 
OWNER           INDEX_NAME      INDEX_TYPE      TABLE_NAME      UNIQUENES STATUS
--------------- --------------- --------------- --------------- --------- --------
SYS             PK_ID1_IND      NORMAL          TEST            UNIQUE    VALID
 
sys@ORCL>select constraint_name,constraint_type,table_name,status,index_owner,index_name 
  2  from dba_constraints where table_name='TEST';
 
CONSTRAINT_NAME      C TABLE_NAME      STATUS   INDEX_OWNER     INDEX_NAME
-------------------- - --------------- -------- --------------- ---------------
PK_ID1_CON           P TEST            DISABLED