How to add primary key on existing data of table(ORA-02437)(给已存在数据表增加主键)

如果一个表已存在数据(ENABLE NOVALIDATE validating existing data),想要增加一个主键不验证以前的数据，这样做是可以的，约束有几种状态
• DISABLE NOVALIDATE
• DISABLE VALIDATE
• ENABLE NOVALIDATE
• ENABLE VALIDATE



不加增加主键有一点问题，下面做一个实验

sql>conn anbob/anbob
connected!
sql>l
1  create or replace procedure p_createtab(name varchar2)
2  is
3  v_sql varchar2(400);
4  begin
5  v_sql := 'create table '||name||' (id int,name varchar2(30))';
6  execute immediate v_sql;
7* end;
Procedure created
SQL> exec p_createtab('testcons');
PL/SQL procedure successfully completed.
--以上纯粹是为了以后偷懒，如果你以上报错，请看我以前写的帖子
SQL>desc  testcons;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER(38)
NAME                                               VARCHAR2(30)
SQL> select * from testcons;
no rows selected
SQL> insert into testcons values(1,'anbob.com');
1 row created.
SQL> insert into testcons values(2,'sesebook.com');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table testcons add constraint pk_id primary key(id) ;
Table altered.
SQL> insert into testcons values(3,'sesebook.com');
1 row created.
SQL>commit;
Commit complete.
SQL> insert into testcons values(3,'sesebook.com');
insert into testcons values(3,'sesebook.com')
*
ERROR at line 1:
ORA-00001: unique constraint (ANBOB.PK_ID) violated

SQL> alter table testcons drop constraint pk_id;
Table altered.
---有数据，如果符合验证默认填加约束是可以的
SQL> select * from user_indexes where table_name='TESTCONS';
no rows selected
SQL>select * from testcons;
ID NAME
---------- ------------------------------------------------------------
1 anbob.com
2 sesebook.com
3 sesebook.com
SQL>insert into testcons values(3,'sesebook.com');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from testcons;
ID NAME
---------- ------------------------------------------------------------
1 anbob.com
2 sesebook.com
3 sesebook.com
3 sesebook.com
SQL>alter table testcons add constraint pk_id primary key(id) ;
alter table testcons add constraint pk_id primary key(id)
*
ERROR at line 1:
ORA-02437: cannot validate (ANBOB.PK_ID) - primary key violated
SQL> alter table testcons add constraint pk_id primary key(id) enable novalidate;
alter table testcons add constraint pk_id primary key(id) enable novalidate
*
ERROR at line 1:
ORA-02437: cannot validate (ANBOB.PK_ID) - primary key violated

note:
--会发现这样也不行，其实是有原因的，加主键约束，会隐式增加一个索引（如果这个字段没索引），解决方法是需要手工建立一个非唯一索引。

SQL> create index idx_testcons_id on testcons(id);
Index created.
SQL> alter table testcons add constraint pk_id primary key(id) enable novalidate;
Table altered.
SQL> select * from testcons;
ID NAME
---------- ------------------------------------------------------------
1 anbob.com
2 sesebook.com
3 sesebook.com
3 sesebook.com
SQL> insert into testcons values(1,'anbob.com');
insert into testcons values(1,'anbob.com')
*
ERROR at line 1:
ORA-00001: unique constraint (ANBOB.PK_ID) violated

SQL> insert into testcons values(4,'weejar.com');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from testcons;
ID NAME
---------- ------------------------------------------------------------
1 anbob.com
2 sesebook.com
3 sesebook.com
3 sesebook.com
4 weejar.com

-- over--