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/16719800/viewspace-675845/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16719800/viewspace-675845/