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

[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这个特性很有意思，可以实现只读表的功能。