关于enable novalidate，对于误删除唯一索引的补救办法-CSDN博客

SQL> create table t1 (t_id number);

Table created.

SQL> select index_name from user_indexes where table_name='T1';

no rows selected

SQL> insert into t1 values(110);

1 row created.

SQL> insert into t1 values(120);

1 row created.

SQL> insert into t1 values(130);

1 row created.

SQL> insert into t1 values(140);

1 row created.

SQL> create index idx_t1_id on t1(id) online;
create index idx_t1_id on t1(id) online
*
ERROR at line 1:
ORA-00904: "ID": invalid identifier

SQL> create index idx_t1_id on t1(t_id) online;

Index created.

SQL> select * from t1;

T_ID
----------
110
120
130
140

SQL> commit
2 ;

Commit complete.

SQL> alter table t1 add constraint pk_t1_id primary key(t_id) enable validate;

Table altered.

SQL> insert into t1 values(110);
insert into t1 values(110)
*
ERROR at line 1:
ORA-00001: unique constraint (LUDA.PK_T1_ID) violated

SQL>
enable validate 是对当前存在的数据进行唯一性效验。如果当前表中存在重复数据，那么添加pk_t1_id的主键就会出错。

下面来测试
enable novalidate

SQL> drop table t1
2 ;

Table dropped.

SQL>
SQL> create table t1 (t_id number);

Table created.

SQL> insert into t1 values(110);

1 row created.

SQL> insert into t1 values(120);

1 row created.

SQL> insert into t1 values(120);

1 row created.

SQL> insert into t1 values(130);

1 row created.

SQL> create index idx_t1_id on t1(t_id);

Index created.

SQL> alter table t1 add constraint pk_t1_id primary key(t_id) enable validate;
alter table t1 add constraint pk_t1_id primary key(t_id) enable validate
*
ERROR at line 1:
ORA-02437: cannot validate (LUDA.PK_T1_ID) - primary key violated

SQL> alter table t1 add constraint pk_t1_id primary key(t_id) enable novalidate;

Table altered.

SQL> insert into t1 values(120);
insert into t1 values(120)
*
ERROR at line 1:
ORA-00001: unique constraint (LUDA.PK_T1_ID) violated

SQL>

这里novalidate的作用就是不对旧的数据进行效验，只对新加进来的数据进行效验。

[@more@]

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

转载于:http://blog.itpub.net/24487368/viewspace-1043313/