唯一索引误删除之后补救措施

详细请查看：http://www.itpub.net/thread-1379575-2-1.html

这是enable novalidate的作用测试希望对你有点用

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的作用就是不对旧的数据进行效验，只对新加进来的数据进行效验。

xzh2000是建议加唯一约束索引，索引这里的创建方法
alter table table_name add constraint uk_table_name unique(column_name)
enable novalidate;