[20120824]oracle中的 CONSTRAINT 属性ENABLE DISABLE VALIDATE NOVALIDATE.txt
今天要在生产系统中建立一个约束,犯了一点点小错误,看书复习记录一下:
我参考了这个链接:
http://www.databasedesign-resource.com/enabling-and-disabling-oracle-constraints.html
http://space.itpub.net/13387766/viewspace-617654
一、组合特性说明:
是否要求满足约束 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相同,禁用约束,删除约束上的索引,且允许修改被约束的记录。
如果设置约束是Disable Validate这个特性很有意思,可以实现只读表的功能。
不过11G允许设置表为只读。
测试如下:
1.建立测试例子:
create table a (id number);
insert into a select rownum from dual connect by level <=10;
commit ;
alter table a add constraint ck_a check (id<=100) disable;
2.测试:
SQL> select status,validated from dba_constraints where wner=user and constraint_name='CK_A';
STATUS VALIDATED
-------- -------------
DISABLED NOT VALIDATED
--可以发现状态是DISABLED和NOT VALIDATED,这样可以DML数据:
SQL> insert into a values(199);
1 row created.
SQL> rollback ;
Rollback complete.
3.修改约束属性:
SQL> alter table a disable validate constraint ck_a;
Table altered.
SQL> select status,validated from dba_constraints where wner=user and constraint_name='CK_A';
STATUS VALIDATED
-------- -------------
DISABLED VALIDATED
--可以发现状态是DISABLED和NOT VALIDATED,这样就变成了只读表。
SQL> insert into a values(199);
insert into a values(199)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.CK_A) disabled and validated
SQL> insert into a values(19);
insert into a values(19)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.CK_A) disabled and validated
SQL> update a set id =10 where id=1;
update a set id =10 where id=1
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.CK_A) disabled and validated
SQL> delete from a where id=1;
delete from a where id=1
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.CK_A) disabled and validated
4.删除约束:
SQL> alter table a drop constraint ck_a;
Table altered.
5.我前面使用约束条件是id<100.如果换成1=1就可以实现同样的功能。
--SQL> alter table a add constraint ck_a check (1=1) disable novalidate;
SQL> alter table a add constraint ck_a check (1=1) disable;
Table altered.
SQL> alter table a modify constraint ck_a disable validate;
Table altered.
--oracle 的语法真奇怪,前面我使用的是alter table a disable validate constraint ck_a;也可以。
--还是这里使用的语法更好1点。
SQL> insert into a values(19);
insert into a values(19)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.CK_A) disabled and validated
6.我的测试环境是11G:
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> alter table a drop constraint ck_a;
Table altered.
SQL> alter table a read only ;
Table altered.
SQL> insert into a values(19);
insert into a values(19)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."A"
--取消只读特性。
SQL> alter table a read write ;
Table altered.
今天要在生产系统中建立一个约束,犯了一点点小错误,看书复习记录一下:
我参考了这个链接:
http://www.databasedesign-resource.com/enabling-and-disabling-oracle-constraints.html
http://space.itpub.net/13387766/viewspace-617654
一、组合特性说明:
是否要求满足约束 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相同,禁用约束,删除约束上的索引,且允许修改被约束的记录。
如果设置约束是Disable Validate这个特性很有意思,可以实现只读表的功能。
不过11G允许设置表为只读。
测试如下:
1.建立测试例子:
create table a (id number);
insert into a select rownum from dual connect by level <=10;
commit ;
alter table a add constraint ck_a check (id<=100) disable;
2.测试:
SQL> select status,validated from dba_constraints where wner=user and constraint_name='CK_A';
STATUS VALIDATED
-------- -------------
DISABLED NOT VALIDATED
--可以发现状态是DISABLED和NOT VALIDATED,这样可以DML数据:
SQL> insert into a values(199);
1 row created.
SQL> rollback ;
Rollback complete.
3.修改约束属性:
SQL> alter table a disable validate constraint ck_a;
Table altered.
SQL> select status,validated from dba_constraints where wner=user and constraint_name='CK_A';
STATUS VALIDATED
-------- -------------
DISABLED VALIDATED
--可以发现状态是DISABLED和NOT VALIDATED,这样就变成了只读表。
SQL> insert into a values(199);
insert into a values(199)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.CK_A) disabled and validated
SQL> insert into a values(19);
insert into a values(19)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.CK_A) disabled and validated
SQL> update a set id =10 where id=1;
update a set id =10 where id=1
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.CK_A) disabled and validated
SQL> delete from a where id=1;
delete from a where id=1
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.CK_A) disabled and validated
4.删除约束:
SQL> alter table a drop constraint ck_a;
Table altered.
5.我前面使用约束条件是id<100.如果换成1=1就可以实现同样的功能。
--SQL> alter table a add constraint ck_a check (1=1) disable novalidate;
SQL> alter table a add constraint ck_a check (1=1) disable;
Table altered.
SQL> alter table a modify constraint ck_a disable validate;
Table altered.
--oracle 的语法真奇怪,前面我使用的是alter table a disable validate constraint ck_a;也可以。
--还是这里使用的语法更好1点。
SQL> insert into a values(19);
insert into a values(19)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.CK_A) disabled and validated
6.我的测试环境是11G:
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> alter table a drop constraint ck_a;
Table altered.
SQL> alter table a read only ;
Table altered.
SQL> insert into a values(19);
insert into a values(19)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."A"
--取消只读特性。
SQL> alter table a read write ;
Table altered.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-741814/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-741814/