Constraint enable novalidate cause ORA-02437 error

问题提出 by empo007

关于约束ENABLE NOVALIDATE的一个疑问
create table test
(id varchar2(12),
name varchar2(30) ,constraint pk_test primary k

alter table test disable constraint pk_test

然后向表中插入两条完全一样的数据并commit

我的印象是ENABLE NOVALIDATE并不检查已经存在的数

SQL> alter table test enable novalidate constra
alter table test enable novalidate constraint p
*
ERROR 位于第 1 行:
ORA-02437: 无法验证 (DBO.PK_TEST) - 违反主键

[@more@]

我的测试及解决方法

这个问题实质上是由primary key所依赖的唯一键索引起的，如下以两种方式做一下测试，第二种为解决问题的方法。

1、你的方式,会报错，因为此时primary key用的是唯一銉索引

SQL> create table t1
2 (id number,name varchar2(10),constraint pk_t1 primary key (id));

Table created.

SQL> select owner,constraint_name,constraint_type,status
2 from user_constraints where table_name='T1';

OWNER CONSTRAINT_NAME C STATUS
------------- ------------------------------ - --------
HR PK_T1 P ENABLED

SQL> select table_name,index_name,UNIQUENESS
2 from user_indexes where table_name='T1'
3 /

TABLE_NAME INDEX_NAME UNIQUENES
------------- ------------------------------ ---------
T1 PK_T1 UNIQUE

SQL> alter table t1
2 disable novalidate constraint pk_t1;

Table altered.

SQL> select owner,constraint_name,constraint_type,status
2 from user_constraints where table_name='T1';

OWNER CONSTRAINT_NAME C STATUS
------------- ------------------------------ - --------
HR PK_T1 P DISABLED

SQL> insert into t1 values(1,'yang');

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> alter table t1
2 enable novalidate constraint pk_t1;
alter table t1
*
ERROR at line 1:
ORA-02437: cannot validate (HR.PK_T1) - primary key violated

2、另一种方式，建一普通的索引

SQL> drop table t1;

Table dropped.

SQL> create table t1 (id number,name varchar2(10));

Table created.

SQL> create index idx_id_t1 on t1(id);

Index created.

SQL> select table_name,index_name,UNIQUENESS
2 from user_indexes where table_name='T1'
2 /

TABLE_NAME INDEX_NAME UNIQUENES
------------- ------------------------------ ---------
T1 IDX_ID_T1 NONUNIQUE

SQL> alter table t1
2 add constraint pk_t1 primary key(id) using index idx_id_t1;

Table altered.

SQL> alter table t1 disable novalidate primary key;

Table altered.

SQL> insert into t1 values(1,'yang')
2 /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t1 enable novalidate primary key;

Table altered.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/717880/viewspace-831603/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/717880/viewspace-831603/