在线给重复记录列添加主键

背景：

一次误操作，某表主键被删除，索引丢失，表中插入了大量重复数据。

由于索引丢失，查询速度明显降低。

在不停应用的情况下，如何快速解决问题。

解决过程描述：

1.先给要添加的主键列添加索引

 

2.添加主键指定enable novalidate属性
alter table &table_name  add constraint &primary_key primary key (&col) enable novalidate;

 

3.删除重复记录

 

4.恢复约束为enable validate状态
alter table &table_name modify  constraint &primary_key enable validate

 

实验：

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as report

SQL> create table EMP
  2      (
  3        EMP_NO NUMBER(2),
  4        SALARY NUMBER(8,2)
  5      );

Table created

--1.插入两条EMP_NO为1的重复数据

SQL> insert into EMP (EMP_NO, SALARY) values (1, 100);
1 row inserted
SQL> insert into EMP (EMP_NO, SALARY) values (1, 100);
1 row inserted
SQL> insert into EMP (EMP_NO, SALARY) values (2, 200);
1 row inserted
SQL> commit;
Commit complete

 

--2.模拟直接创建主键

SQL> alter table EMP  add constraint emp_pk primary key (EMP_NO);
alter table EMP  add constraint emp_pk primary key (EMP_NO)
ORA-02437: 无法验证 (REPORT.EMP_PK) - 违反主键

SQL> alter table EMP  add constraint emp_pk primary key (EMP_NO) enable novalidate;
alter table EMP  add constraint emp_pk primary key (EMP_NO) enable novalidate
ORA-02437: 无法验证 (REPORT.EMP_PK) - 违反主键

 

--3.创建索引

SQL> create index emp_idx on EMP(emp_no);
Index created

 

--4.创建主键
SQL> alter table EMP  add constraint emp_pk primary key (EMP_NO) enable novalidate;
Table altered

enable novalidate 只对新插入的数据生效，对历史数据不进行检查。

 

SQL> select * from emp;
EMP_NO     SALARY
------ ----------
     1     100.00
     1     100.00
     2     200.00

 

--5.删除重复记录

SQL> delete from emp a
  2   where a.rowid != (select max(rowid) from emp b where a.emp_no = b.emp_no);

1 row deleted

SQL> commit;

Commit complete

 

SQL>  insert into EMP (EMP_NO, SALARY) values (1, 100);

insert into EMP (EMP_NO, SALARY) values (1, 100)

ORA-00001: 违反唯一约束条件 (REPORT.EMP_PK)

SQL>  insert into EMP (EMP_NO, SALARY) values (3, 300);

1 row inserted

SQL> commit;

Commit complete

 

--6启用约束立即生效

SQL> alter table emp modify constraint emp_pk  enable validate;

Table altered

 

 

约束的四种状态

enable( validate) :启用约束,对历史数据和更新数据进行约束检查。
enable novalidate :启用约束,仅对新数据强制执行约束,对历史数据不进行检查。
disable( novalidate):关闭约束,可以对约束列的数据进行修改等操作.
disable validate :关闭约束,不能对表进行 插入/更新/删除等操作.

 

详见：http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/general005.htm#ADMIN11547