[20160526]建立主鍵問題.txt

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

                             *

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')

*

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')

                     *

ORA-01400: cannot insert NULL into ("SCOTT"."T"."ID")

--剩下重複的記錄的問題開發自己解決.