我们想保持既有违反约束的数据存在,但新加的数据必须保正满足约束,这个时候,就可以采用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的测试.
|