[20231115]建立enable novalidate约束2.txt

--//昨天优化生产系统发现有1个表没有任何索引，而且还很大。当我想当然建立主键索引时遇到了问题，主键重复，无法建立主键索引。

> select JLXH,jgid,count(*) from YB_GJYB_ZYDJXX group by JLXH,jgid having count(*)>=2;
      JLXH       JGID   COUNT(*)
---------- ---------- ----------
     68897          1          2
--//有2条记录重复.我仔细比较这两条记录发现内容完全一样。
--//也就是我不能建立唯 一索引，只能建立普通索引。但是我想避免以后不再出现这样的情况。
--//好像可以通过建立enable novalidate约束，避免以后再出来类似问题，在测试环境测试看看，并做一个记录。

1.环境：
SCOTT@book> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create table deptx as select * from dept;
Table created.

SCOTT@book> insert into deptx select * from dept where deptno=40;
1 row created.

SCOTT@book> commit ;
Commit complete.
--//分析略。

2.测试：

SCOTT@book> create unique index pk_deptx on deptx(deptno);
create unique index pk_deptx on deptx(deptno)
                                *
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
--//有重复值,无法建立unique索引.

SCOTT@book> create index pk_deptx on deptx(deptno);
Index created.

SCOTT@book> alter table scott.deptx add constraint pk_deptx primary key (deptno) enable novalidate;
Table altered.

SCOTT@book> insert into deptx select * from dept where deptno =10;
insert into deptx select * from dept where deptno =10
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_DEPTX) violated
--//这样以后不可能插入重复值.

SCOTT@book> update deptx set deptno =10 where deptno=40 and rownum=1;
update deptx set deptno =10 where deptno=40 and rownum=1
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_DEPTX) violated

SCOTT@book> update deptx set deptno=50 where deptno=40 and rownum=1;
1 row updated.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> select * from deptx;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        50 OPERATIONS     BOSTON
        40 OPERATIONS     BOSTON

--//一些细节可以参考以前写的blog:
--//[20120824]oracle中的 CONSTRAINT 属性ENABLE DISABLE VALIDATE NOVALIDATE.txt ==> http://blog.itpub.net/267265/viewspace-741814/

是否要求满足约束   Validate                     Novalidate
------------------------------------------------------------------------------------
                   已有记录     新增/修改记录   已有记录    新增/修改记录
Enable             Yes　    　  Yes             No　         Yes
Disable            Yes　        No              No           No

--//Validate确保已有数据符合约束；
--//Novalidate不必考虑已有数据是否符合约束。
--//除非Novalidate被指定，Enable默认Validate；
--//除非Validate被指定，Disable默认Novalidate；

--//Validate和Novalidate对Enable和Disable没有任何默认暗示。
--//Enable Validate与Enable相同，检查已有记录和新增记录，确保都符合约束；
--//Enable Novalidate 允许已有记录不必满足约束条件，但新增/修改的记录必须满足；
--//Disable Validate禁用约束，删除约束上的索引，不允许修改任何被约束的记录；
--//Disable Novalidate与Disable相同，禁用约束，删除约束上的索引，且允许修改被约束的记录。

--//如果设置约束是Disable Validate这个特性很有意思，可以实现只读表的功能。