关于constraint enable novalidate的困惑,请高手指点

显示全部楼层 · 2010-3-1 11:07:27

今天在10.2.0.1上测试了一下enablenovalidate功能,以下是整个过程:

SQL> conn scott@oradb
输入口令: *****
已连接。
INSTANCE_NAMEHOST_NAME
VERSION STARTUP_TIMESTATUS DATABASE_STATUS
---------------- -------------------- ----------------- ------------------- ------------ -----------------
oradb
orasvr
10.2.0.1.02006-07-18 13:58:41 OPEN ACTIVE
SQL> drop table test1 cascade constraints purge;
表已删除。
SQL> create table test1
2(id int,
3name varchar2(40),
4constraint pk_test1 primary key (id) disable);
表已创建。
SQL> select constraint_name,constraint_type,status from user_constraints
2where table_name = 'TEST1';
CONSTRAINT_NAME
C STATUS
------------------------------ - --------
PK_TEST1
P DISABLED
SQL> insert into test1
2values(1,'sdfsdf');
已创建 1 行。
SQL> /
已创建 1 行。
SQL> /
已创建 1 行。
SQL> /
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from test1;
ID NAME
---------- ----------------------------------------
1 sdfsdf
1 sdfsdf
1 sdfsdf
1 sdfsdf
SQL> alter table test1 modify constraint pk_test1 enable novalidate;
alter table test1 modify constraint pk_test1 enable novalidate
*
第 1 行出现错误:
ORA-02437: cannot validate (SCOTT.PK_TEST1) - primary key violated

SQL>
SQL> delete from test1 a
2where rowidselect * from test1;
ID NAME
---------- ----------------------------------------
1 sdfsdf
SQL> alter table test1 modify constraint pk_test1 enable novalidate;
表已更改。
SQL> select constraint_name,constraint_type,status from user_constraints
2where table_name = 'TEST1';
CONSTRAINT_NAME
C STATUS
------------------------------ - --------
PK_TEST1
P ENABLED
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE10.2.0.1.0Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
我的问题是,enable novalidate是存在的数据不进行校验,只是对新加入的进行校验,难道是我的操作有问题,请高手指点

千问 · 2010-3-1 11:07:27

自己定一下

千问 · 2010-3-1 11:07:27

我也一直和lz的想法一样，做了个测试，结果也和lz一样。不明白了。。。

千问 · 2010-3-1 11:07:27

If you enable a unique or primary key constraint, and if no index exists on the key, Oracle creates a unique index. This index is dropped if the constraint is subsequently disabled, and Oracle rebuilds the index every time the constraint is enabled.
To avoid rebuilding the index and eliminate redundant indexes, create new primary key and unique constraints initially disabled. Then create (or use existing) nonunique indexes to enforce the constraint. Oracle does not drop a nonunique index when the constraint is disabled, so subsequent ENABLE operations are facilitated.

千问 · 2010-3-1 11:07:27

先把约束disable
然后 alter table test1 constraint pk_test1 enable novalidate;
或者直接drop后重新add一个约束，enablenovalidate

千问 · 2010-3-1 11:07:27

This feature has been disabled since Oracle 816 (correct me if I am wrong). You may do it this way:
1. create the index based on the columns to be used as primay key or unique keys (you have to drop the constarints if they have been created with the table, even they are disabled),
2, add the constraint novalidate.
Borrow your example:
12:20:56 @devl> create table test1
12:20:58 2 (id int,
12:20:58 3 name varchar2(40),
12:20:58 4 constraint pk_test1 primary key (id) disable);
Table created.
12:20:58 @devl>
12:20:58 @devl> insert into test1 values(1,'sdfsdf');
1 row created.
12:20:59 @devl> /
1 row created.
12:21:01 @devl> /
1 row created.
12:21:01 @devl> /
1 row created.
12:21:02 @devl> /
1 row created.
12:21:19 @devl> create index pk_test1 on test1(id);
Index created.
12:21:36 @devl> select * from test1;
ID NAME
--------- ---------------------------------------------
1 sdfsdf
1 sdfsdf
1 sdfsdf
1 sdfsdf
1 sdfsdf
5 rows selected.

12:22:13 @devl> alter table test1 drop primary key;
Table altered.
12:22:39 @devl> alter table test1 add constraint pk_test1 primary key(id) novalidate;
Table altered.
12:22:45 @devl>select * from test1;
ID NAME
--------- ---------------------------------------------
1 sdfsdf
1 sdfsdf
1 sdfsdf
1 sdfsdf
1 sdfsdf
5 rows selected.
12:22:51 @devl>insert into test1 values(1,'sdfsdf');
insert into test1 values(1,'sdfsdf')
*
ERROR at line 1:
ORA-00001: unique constraint (ADMINPLUS.PK_TEST1) violated
Sam

千问 · 2010-3-1 11:07:27

知道了,就是说当你enable primary key的时候会自动的创建一个唯一索引,要想实现enable novalidate的功能,也就是对存在的数据不校验,就必须首先在主键列上创建一个非唯一索引,然后让主键使用之:
SQL> alter table test1 drop constraint pk_test1;
表已更改。
SQL> alter table test1 add constraint pk_test1 primary key (id) disable;
表已更改。
SQL> create index ind_id on test1(id);
索引已创建。
SQL> select * from test1;
ID NAME
---------- ----------------------------------------
1 dfsfd
1 dfsfd
1 dfsfd
1 dfsfd
SQL> alter table test1 modify constraint pk_test1 enable novalidate;
表已更改。
SQL> select constraint_name,constraint_type,status from user_constraints
2where table_name = 'TEST1';
CONSTRAINT_NAME
C STATUS
------------------------------ - --------
PK_TEST1
P ENABLED
SQL>