[20160526]建立主键问题.txt
--生产系统有1个表没有主键,要求建立发现无法建立,有重复.而且这个索引查询是需要.
--实际上可以限制以后的记录不再重复,以前我自己也遇到过,做一个记录.
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
create table t (id int,name varchar2(100));
insert into t values(1,'AAAAA');
insert into t values(2,'BBBBB');
insert into t values(3,'CCCCC');
insert into t values(4,'DDDDD');
insert into t values(5,'EEEEE');
commit;
SCOTT@book> insert into t values(3,'ccccc');
1 row created.
SCOTT@book> commit ;
Commit complete.
2.现在有重复:
SCOTT@book> create unique index pk_t on t (id);
create unique index pk_t on t (id)
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
--无法建立唯一索引.先建立索引:
SCOTT@book> create index pk_t on t (id);
Index created.
SCOTT@book> alter table t add constraint pk_t primary key (id) ;
alter table t add constraint pk_t primary key (id)
*
ERROR at line 1:
ORA-02437: cannot validate (SCOTT.PK_T) - primary key violated
--无法加入约束.因为有重复记录.
SCOTT@book> alter table t add constraint pk_t primary key (id) enable novalidate;
Table altered.
SCOTT@book> insert into t values(1,'ccccc');
insert into t values(1,'ccccc')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_T) violated
SCOTT@book> @ &r/desc t
Name Null? Type
----- -------- ----------------
ID NUMBER(38)
NAME VARCHAR2(100)
--虽然ID的定义还没有not null,但是插入空值:
SCOTT@book> insert into t values(null,'ccccc');
insert into t values(null,'ccccc')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."T"."ID")
--剩下重复的记录的问题开发自己解决.