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

原创 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

 

给一个表添加主键列

SQL> drop table t1 purge; 表已删除。 SQL> create table t1 as select *From emp; 表已创建。 SQL> alter table t1 ...
  • wll_1017
  • wll_1017
  • 2012年10月29日 10:46
  • 963

sql给已有表添加主键

/* ALTER TABLE [dbo].[cg_YearAssessZp] ALTER COLUMN YearAssessZpGUID UNIQUEIDENTIFIER NOT NULL; AL...
  • zzx3q
  • zzx3q
  • 2015年01月10日 15:37
  • 2859

给MySql表格添加一个自动增长的主键列

曾经给某个Mysql表格添加主键时,用了以下SQL语句 alter table `表格名` add column `列名` int not null auto_increment comment...
  • romantic_angel
  • romantic_angel
  • 2012年02月27日 10:16
  • 2027

db2 alter 以及 db2 constraint

--以下语句都是在db2中运行的结果,其他的数据库不保证正确。 DROP TABLE DQ; DROP TABLE STU;   create table dq (dqno int,dqnam...
  • DLODJ
  • DLODJ
  • 2012年03月03日 11:41
  • 18187

有重复数据的表添加主键或唯一约束

约束分为几种状态validate/novalidate,enable/disable,可以通过alter table .. modify constraint ....enable/disable  ...
  • gua___gua
  • gua___gua
  • 2014年10月31日 12:50
  • 1188

Postgresql 创建主键并设置自动递增的三种方法

Postgresql 有以下三种方法设置主键递增的方式,下面来看下相同点和不同点。 --方法一 create table test_a  (   id serial,   name ch...
  • zhengbo0
  • zhengbo0
  • 2014年01月13日 11:26
  • 1903

MySQL语句整理(2)

一,备份数据库与恢复数据库备份与恢复的都是数据库中的每个表,如果该数据库不存在需要先建立数据库.--在登录之前,注意该语句后面不能写分号 --看箭头的方向,箭头的方向就代表数据的传输方向 --这句话就...
  • xiaoqigui2017
  • xiaoqigui2017
  • 2017年06月02日 20:36
  • 112

添加列和主键

添加列 alter table table_name add (col1 type,col2 type); ALTER TABLE TABLE_AAA ADD (STOCKMAN NUMBER(1...
  • neusoft06
  • neusoft06
  • 2012年12月13日 20:23
  • 636

主键查询和设置(postgres )

今天库里需要建一张新表,需要三个字段联合作为主键。在Navicat里是可以通过点击最后一栏设置主键的。 但是我心想,一张表只有一个主键,这个1,2,3表示的是啥啊?心里有点担心的自己的主键有没有...
  • u013992365
  • u013992365
  • 2017年08月01日 17:48
  • 537

sql server 插入 重复列 解决办法

针对所有记录,当数据库里所有name 字段的值不为2,或者 所有ema字段的值不为1 时才执行添加操作 if  not exists (select * from dd where check...
  • yxy353245805
  • yxy353245805
  • 2012年07月16日 15:53
  • 472
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:在线给重复记录列添加主键
举报原因:
原因补充:

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