如果一个表已存在数据(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–