ANBOB™

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

打赏

微信扫一扫，打赏作者吧～