[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")

--剩下重复的记录的问题开发自己解决.