novalidate选项无效的问题:
平台:Oracle9i Enterprise Edition Release 9.2.0.1.0 for Windows
--创建一个带主键约束的表
create table temp(
temp_id number(3) constraint pk_temp primary key,
temp_desc varchar(20) );
--失效主键约束
alter table temp
disable constraint pk_temp;
--插入数据行
insert into temp values(1,'Record One');
insert into temp values(1,'Record Two');
insert into temp values(3,'Record Three');
insert into temp values(4,'Record Four');
--提交
commit;
--使用novalidate选项启用约束
alter table temp
enable novalidate constraint pk_temp;
================================================================
上面这一句报错,why?
alter table temp
*
ERROR at line 1:
ORA-02437: cannot validate (TSP3.PK_TEMP) - primary key violated
=================================================================
--解答:
--因为主键约束没有被定义为可延迟的(deferrable)
--所以,即使使用novalidate启用约束,还是会表的数据上应用约束
---------------------------------
--解决方案:
--删除主键约束
alter table temp
drop primary key keep index;
--重新添加主键约束,状态为可延迟、禁用
alter table temp
add constraint pk_temp primary key(temp_id) deferrable disable;
--使用带novalidate选项来启用约束
alter table temp
enable novalidate constraint pk_temp;
--一切ok