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

原创 2011年01月15日 21:30:00

背景:

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

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

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

解决过程描述:

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

 

相关文章推荐

联合主键 去重复记录的方法

方法1、 就是将三个字段连接起来作为主键,进行数据是否重复的判断。这里值得注意的是某列的值可能为空,所以要赋一个空字符串过去: select * from t1 where nvl(col1,''...
  • jwlsky
  • jwlsky
  • 2012年05月20日 21:22
  • 2067

oracel重复记录只保留一条

  • 2014年04月01日 14:18
  • 795B
  • 下载

replace into导致mysql自增列导致主键重复问题分析

前几天开发童鞋反馈一个利用load data infile命令导入数据主键冲突的问题,分析后确定这个问题可能是mysql的一个bug,这里提出来给大家分享下。以免以后有童鞋遇到类似问题百思不得其解,难...
  • xtjsxtj
  • xtjsxtj
  • 2015年06月13日 22:32
  • 2134

MySQL删除重复记录

  • 2008年03月21日 11:26
  • 3KB
  • 下载

MySql避免重复插入记录(根据主键判重)

今天用python抓取数据入库需要避免重复数据插入,在网上找了一些方法: 方案一:使用ignore关键字 如果是用主键primary或者唯一索引unique区分了记录的唯一性,避免重复插入...

删重复记录

  • 2013年05月12日 11:22
  • 386B
  • 下载

oracle 快速删除大批量数据方法(全部删除,条件删除,删除大量重复记录)

全部删除如果是删除某个表的所有数据,并且不需要回滚,使用 TRUNCATE 就ok了。关于Trancate 参见这里http://blog.csdn.net/gnolhh168/archive/201...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:在线给重复记录列添加主键
举报原因:
原因补充:

(最多只允许输入30个字)