问题描述:做数据迁移时源表有重复记录,且有主键,数据迁移至目标表后,先针对表insert 数据,后创建索引及主键,发现不能创建主键,因为数据有重复记录,则考虑按下面方式:
1.先创建索引,后在索引上创建novalidate 主键
例:SQL>create table test( a number ,b number); SQL>insert into test(1,2); SQL> insert into test(1,3);
SQL>create index on test(a) tablespace tab_idx; SQL>alter table test add constraint primary key on(a) using index novalidate;
(转载:)
Enable/Disable/Validate/Novalidate
Enable/Disable/Validate/Novalidate
2007/06/25
一、组合特性说明:
是否要求满足约束 Validate Novalidate
已有记录 新增/修改记录 已有记录 新增/修改记录
Enable Yes Yes No Yes
Disable Yes No No No
Validate确保已有数据符合约束;
Novalidate不必考虑已有数据是否符合约束。
除非Novalidate被指定,Enable默认Validate;
除非Validate被指定,Disable默认Novalidate;
Validate和Novalidate对Enable和Disable没有任何默认暗示。
Enable Validate与Enable相同,检查已有记录和新增记录,确保都符合约束;
Enable Novalidate 允许已有记录不必满足约束条件,但新增/修改的记录必须满足;
Disable Validate禁用约束,删除约束上的索引,不允许修改任何被约束的记录;
Disable Novalidate与Disable相同,禁用约束,删除约束上的索引,且允许修改被约束的记录。
Alter table table_name
Enable/Disable [Validate/Novalidate] constraint constraint_name;
Alter table table_name
Modify constraint constraint_name
Enable/Disable/Validate/Novalidate/
Enable Validate/Enable Novalidate/Disable Validate/Disable Novalidate;
Alter table table_name -----错误语法
Validate/Novalidate constraint constraint_name; -----Alter table不能直接指定Validate/Novalidate
注:以上为Check约束语法,不同的约束类型语法不尽相同,请自行分析。
二、EG:
-------------------------------------------------------------------------------------------
SQL> create table test(id int,name varchar2(10));
Table created.
SQL> alter table test
2 add constraint ck_id check(id>10);
Table altered.
-------------------------------------------------------------------------------------------
测试一:Enable Validate
-------------------------------------------------------------------------------------------
SQL> Alter table test
2 Enable validate constraint ck_id;
Table altered.
SQL> insert into test values(5,'Oracle');
insert into test values(5,'Oracle')
*
ERROR at line 1:
ORA-02290: check constraint (EWORM.CK_ID) violated
SQL> insert into test values(17,'ERP');
1 row created.
-------------------------------------------------------------------------------------------
测试二:Enable Novalidate
-------------------------------------------------------------------------------------------
SQL> alter table test
2 disable constraint ck_id;
Table altered.
SQL> insert into test values(5,'Oracle');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
ID NAME
---------- ----------
17 ERP
5 Oracle
2 rows selected.
SQL> alter table test
2 enable novalidate constraint ck_id;
Table altered.
SQL> insert into test values(32,'SAP');
1 row created.
SQL> insert into test values(3,'Linux');
insert into test values(3,'Linux')
*
ERROR at line 1:
ORA-02290: check constraint (EWORM.CK_ID) violated
-------------------------------------------------------------------------------------------
测试三:Disable Validate
-------------------------------------------------------------------------------------------
SQL> delete from test where id<10;
2 rows deleted.
SQL> commit;
Commit complete.
SQL> alter table test
2 disable validate constraint ck_id;
Table altered.
SQL> select * from test;
ID NAME
---------- ----------
17 ERP
32 SAP
13 Windows
3 rows selected.
SQL> update test set name='Change' where id=17;
update test set name='Change' where id=17
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (EWORM.CK_ID)
disabled and validated
-------------------------------------------------------------------------------------------
测试四:Disable Novalidate
-------------------------------------------------------------------------------------------
SQL> alter table test
2 disable novalidate constraint ck_id;
Table altered.
SQL> insert into test values(2,'Linux');
1 row created.
SQL> insert into test values(13,'Windows');
1 row created.
SQL> update test set name = 'Change' where id=17;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from test;
ID NAME
---------- ----------
17 Change
5 Oracle
32 SAP
2 Linux
13 Windows
5 rows selected.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/297293/viewspace-592674/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/297293/viewspace-592674/