enable novalidate的使用

郑全

我们想保持既有违反约束的数据存在,但新加的数据必须保正满足约束,这个时候,就可以采用enable novalidate来实现.

下面以实际例子来说明:
例子表:
SQL> desc emp5
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID                                        NUMBER(6)
LAST_NAME                                          VARCHAR2(20)
EMAIL                                     NOT NULL VARCHAR2(25)
PHONE_NUMBER                                       VARCHAR2(20)
HIRE_DATE                                 NOT NULL DATE
JOB_ID                                    NOT NULL VARCHAR2(10)
SALARY                                             NUMBER(8,2)
COMMISSION_PCT                                     NUMBER(2,2)
MANAGER_ID                                         NUMBER(6)
DEPARTMENT_ID                                      NUMBER(4)


目前没有主健约束
SQL> select constraint_name,constraint_type,VALIDATED,status from user_constraints where table_name='EMP5';
CONSTRAINT_NAME      C VALIDATED     STATUS
-------------------- - ------------- --------
SYS_C007401          C VALIDATED     ENABLED
SYS_C007402          C VALIDATED     ENABLED
SYS_C007403          C VALIDATED     ENABLED

表的数据如下:
EMPLOYEE_ID
-----------
        102
        103
        104
        107
        124
        141
        142
        143
        144
        149
        174
EMPLOYEE_ID
-----------
        176
        178
        200
        201
        202
        205
        206
        101

然后,我们插入一个employee_id重复的数据,
SQL> insert into emp5(employee_id,email,hire_date,job_id) values(101,'zq',sysdate,'zq');
         commit;
再看:
SQL> select employee_id from emp4;
EMPLOYEE_ID
-----------
        102
        103
        104
        107
        124
        141
        142
        143
        144
        149
        174
EMPLOYEE_ID
-----------
        176
        178
        200
        201
        202
        205
        206
        101
        101
20 rows selected.

已经有重复值了,再去增加主健约束:

SQL> alter table emp5
  2     modify (employee_id primary key enable  novalidate)
  3  ;
alter table emp5
*
ERROR at line 1:
ORA-02437: cannot validate (HR1.SYS_C007404) - primary key violated

这里不行的原因是,有唯一索引导致,
先给主健字段创建非唯一索引:
SQL> create index idx_emp5_empid on emp5(employee_id);
Index created.
再去增加  enable  novalidate 主键约束,成功.
SQL> alter table emp5
  2     modify (employee_id primary key enable  novalidate)
  3  ;
Table altered.
SQL>

我们发现现在数据中,有重复的:

SQL>  select employee_id from emp5;
EMPLOYEE_ID
-----------
        102
        103
        104
        107
        124
        141
        142
        143
        144
        149
        174
EMPLOYEE_ID
-----------
        176
        178
        200
        201
        202
        205
        206
        101
        101
20 rows selected.

检查约束情况;
SQL> select constraint_name,constraint_type,VALIDATED,status from user_constraints where table_name='EMP5';
CONSTRAINT_NAME      C VALIDATED     STATUS
-------------------- - ------------- --------
SYS_C007401          C VALIDATED     ENABLED
SYS_C007402          C VALIDATED     ENABLED
SYS_C007403          C VALIDATED     ENABLED
SYS_C007405          P NOT VALIDATED ENABLED


索引,非唯一索引:

SQL> R
  1* select index_name,index_type,uniqueness from user_indexes where table_name='EMP5'
INDEX_NAME                     INDEX_TYPE                  UNIQUENES
------------------------------ --------------------------- ---------
IDX_EMP5_EMPID                 NORMAL                      NONUNIQUE

再去新插入数据:
SQL> insert into emp5(employee_id,email,hire_date,job_id) values(101,'zq',sysdate,'zq');
insert into emp5(employee_id,email,hire_date,job_id) values(101,'zq',sysdate,'zq')
*
ERROR at line 1:
ORA-00001: unique constraint (HR1.SYS_C007405) violated

可以看到新的数据插入,就会去判断是否重复了.

到此,我们完成了enable novalidate的测试.