`

oracle constraint的属性

阅读更多

constraint有三个属性:

deferrable deferred validated;

其中 deferrable和deferred联合使用,可以控制何时判断数据完整 deferrable defered就可以在commit时再判断;

其中validated和enable联合使用,可以允许旧数据的不完整 enable not validated时,即可。

 

然后转一篇文章,讲解很详细:

转(http://space.itpub.net/17203031/viewspace-704016

 

Oracle约束constraint是我们经常使用的一种数据库规则对象。constraint在数据库中的作用就是从静态角度对数据完整性进行维护。我们经常使用的主键primary key和外键foreign key,本质上就是约束的一种形式。

 

Oracle的约束,我们有三个属性可以进行设置,分别为deferrabledeferredvalidated。针对不同的需求设计场景,采用不同类型的属性,可以帮助我们实现不同的约束效果。下面我们分别来进行实验

 

1、环境准备

 

首先我们还是准备数据实验环境。

 

 

SQL> create table t (id number);

Table created

 

SQL> alter table T

 2   add constraint c_t_id1

 3   check (id>5);

 

Table altered

 

 

我们创建了数据表T,在列id上添加了约束c_t_id1。约束内容很简单,就是要求id值保证是大于5。约束c_t_id1使用的是默认选项,数据字典中对该约束的表示如下:

 

 

SQL> select constraint_name, constraint_type ctype, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from dba_constraints where table_name='T' and wner='SYS';

 

CONSTRAINT_NAME     CTYPE COND      STATUS  DEFERRABLE    DEFERRED VALIDATED

-------------------- ----- ---------- -------- -------------- --------- -------------

C_T_ID1             C    id>5      ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

 

 

注意,此时约束的三个属性取值分别为:deferrable: not deferrable;deferred:immediate;validated:validated;

 

我们观察一下此时数据表的插入现象:

 

 

SQL> insert into t values (1);//插入非法的数据;

insert into t values (1)

 

ORA-02290: 违反检查约束条件 (SYS.C_T_ID1)//立刻报错,将数据剔除!

 

SQL> insert into t values (6);

1 row inserted

 

SQL> commit;

Commit complete

 

SQL> select * from t;

 

       ID

----------

        6

 

 

结论:在默认情况下,Oracle的约束是不允许延迟(not deferrable)、立即应用和验证的(immediate、validated)。在数据变化的时候,立即进行约束验证。

 

2、deferrable:约束应用可以延迟

 

deferrable默认值为not deferrable,字面含义是不可延迟。那么我们如果设置可以延迟,效果是什么呢?

 

 

SQL> alter table T

 2   drop constraint C_T_ID1;

 

Table altered

 

SQL> alter table T

 2   add constraint C_T_ID1

 3   check (id>5)

 4   deferrable;

 

Table altered

 

 

此时,数据字典中的情况是如下:

 

 

SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from dba_constraints where table_name='T' and wner='SYS';

 

CONSTRAINT_NAME     CTYPE STATUS  DEFERRABLE    DEFERRED VALIDATED

-------------------- ----- -------- -------------- --------- -------------

C_T_ID1             C     ENABLED DEFERRABLE    IMMEDIATE VALIDATED

 

 

与默认情况相比,deferrable属性变化为了deferrable。我们观察一下现象:

 

 

SQL> insert into t values (3);

insert into t values (3)

 

ORA-02290: 违反检查约束条件 (SYS.C_T_ID1)

 

 

在插入数据的时候,立即进行约束验证。和默认情况下没有差异。那么怎么处理呢?

 

//手工设置deferred属性为deferred

SQL> set constraint c_t_id1deferred;

Constraints set

 

SQL> insert into t values (3);//此时插入数据时候,并不进行验证操作了。

1 row inserted

 

SQL> insert into t values (7);

1 row inserted

 

SQL> commit;

commit

 

ORA-02091: 事务处理已回退

ORA-02290: 违反检查约束条件 (SYS.C_T_ID1)//直到进行commit的时候,才会应用约束;

 

 

那么,如何设置回原有的属性呢?

 

 

SQL> set constraint c_t_id1immediate;

Constraints set

 

SQL> insert into t values (4);//又恢复插入立刻检查约束的状态了?

insert into t values (4)

 

ORA-02290: 违反检查约束条件 (SYS.C_T_ID1)

 

 

结论:单独deferrable的含义就是是否允许约束检查延后进行。单独设置deferrable为deferrable之后,约束检查延后是不可以直接使用的,要配合deferred参数,如果该参数是immediate,那么约束还是在DML的时候进行应用。如果deferred参数设置为deferred,约束就是在事务commit提交的时候应用,出现错误就连带回滚rollback整个事务。

 

3、deferred:是否进行延迟应用

 

从上面的实验中,我们可以看出deferred属性是配合deferrable属性使用的。当deferrable设置为deferrable之后,可以通过set constraints进行deferred属性的设置,来确定约束应用时点。

 

本部分确定deferred定义的方式和与deferrable属性的关系。是可以在定义约束是使用initially关键字来确定约束的deferred属性取值。

 

 

SQL> alter table T

 2   add constraint C_T_ID1

 3   check (id>5)

 4   deferrable initially deferred;

 

Table altered

 

 

set constraints语句只有在约束的deferrable属性设置为deferrable的时候才可以使用。

 

 

SQL> alter table T

 2   drop constraint C_T_ID1;

 

Table altered

 

SQL> alter table T

 2   add constraint C_T_ID1

 3   check (id>5)

 4 ;

 

Table altered

 

SQL> set constraint c_t_id1 deferred;

 

set constraint c_t_id1 deferred

 

ORA-02447: 无法延迟不可延迟的约束条件

 

 

4、disable禁用约束和validate验证约束

 

disable与validate的关系很紧密,相互制约影响。我们观察下面的实验:

 

 

SQL> alter table t disable constraint c_t_id1;

 

Table altered

 

SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from dba_constraints where table_name='T' and wner='SYS';

 

CONSTRAINT_NAME COND      STATUS  DEFERRABLE    DEFERRED VALIDATED

---------------- ---------- -------- -------------- --------- -------------

C_T_ID1         id>5      DISABLEDNOT DEFERRABLE IMMEDIATENOT VALIDATED

 

 

通过disable constraint语句,可以对一个约束进行禁用操作。禁用disable下的约束,validated属性是not validate,也就不起作用的。

 

 

SQL> select * from t;

 

       ID

----------

        6

        2

        4

       86

 

 

数据表约束禁用后,数据完整性被破坏。此时,如果我们直接进行约束启用。

 

 

SQL> alter table t enable constraint c_t_id1;

 

alter table t enable constraint c_t_id1

 

ORA-02293: 无法验证 (SYS.C_T_ID1) - 违反检查约束条件

 

SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from dba_constraints where table_name='T' and wner='SYS';

 

CONSTRAINT_NAME COND      STATUS  DEFERRABLE    DEFERRED VALIDATED

---------------- ---------- -------- -------------- --------- -------------

C_T_ID1         id>5      DISABLED NOT DEFERRABLE IMMEDIATE NOT VALIDATED

 

 

在约束被启用的时候,会自动进行检验。如果数据不满足条件,Oracle不会开启该约束引用。

 

enable和validate可以配合使用enable novalidate子句使用。

 

 

SQL> alter table t enable novalidate constraint c_t_id1;

Table altered

 

SQL> select constraint_name, SEARCH_CONDITION cond, STATUS, DEFERRABLE, DEFERRED, VALIDATED from dba_constraints where table_name='T' and wner='SYS';

 

CONSTRAINT_NAME COND      STATUS  DEFERRABLE    DEFERRED VALIDATED

---------------- ---------- -------- -------------- --------- -------------

C_T_ID1         id>5      ENABLED NOT DEFERRABLE IMMEDIATENOT VALIDATED

 

 

此时,只是针对现有T中数据不进行验证,对新增加变化的数据,同样会进行验证。

 

SQL> insert into t values (45);

1 row inserted

 

SQL> commit;

Commit complete

 

SQL> insert into t values (3);

insert into t values (3)

 

ORA-02290: 违反检查约束条件 (SYS.C_T_ID1)

 

 

 

5、结论

 

默认情况下,Oracle constraint是不开启延迟约束和原有数据保留验证的。那么在什么样的场景下,我们可以考虑使用这些特性呢?

 

ü       批量数据导入、物化视图刷新的时候,事务量很大,而且存在数据规律前后颠倒的情况。此时,如果开启着立即约束应用的开关,可能存在一些暂时性的约束不满足的情况,从而影响到整个系统的运行。开启约束延迟验证,就可以帮助我们解决这个问题;

ü       历史数据移植。历史数据存在不规则的情况,很多时候难以满足我们新系统的数据完整性要求。可以使用not validate的方式,对历史数据不进行约束控制,而只针对新数据开启管理

分享到:
评论

相关推荐

    Oracle Configurator Constraint Definition Language Guide Release

    Oracle Configurator Constraint Definition Language Guide Release 11i 是一份详细阐述Oracle配置器(Oracle Configurator)约束定义语言(Constraint Definition Language, CDL)的指南,该版本为11i。...

    oracle改属性[文].pdf

    本文将详细解析在Oracle中如何使用`ALTER TABLE`语句来更改表的属性。 首先,`ALTER TABLE`命令用于修改已存在的表的结构,包括添加新列、删除列、修改列的属性、重命名列以及改变表名。例如,要向名为"user"的表中...

    Oracle MSSQL查询表结构属性

    了解了这些查询后,你将能够有效地在Oracle和MSSQL中探索和理解表的结构属性。通过熟练掌握这些技能,数据库管理员和开发人员可以更好地设计、维护和优化数据库。记住,每个数据库系统都有其特定的语法和方法,因此...

    oracle 学习心得与总结

    表是数据库中存储数据的主要结构,由一系列列组成,每列定义了数据类型和属性。约束是确保数据完整性的重要机制,它们限制了可以插入或更新到表中的数据。 3. **约束类型**: - **主键约束**:唯一标识表中的每一...

    Oracle 增加修改删除字段

    在Oracle数据库中,我们经常需要对现有的表结构进行调整,比如增加新的字段、修改现有字段的属性或数据类型、删除不再使用的字段等。这些操作可以通过`ALTER TABLE`命令来完成。本文将详细介绍如何使用`ALTER TABLE`...

    Oracle_基本建表语句

    Oracle数据库是世界上最流行的数据库管理系统之一,它提供了丰富的功能来创建、管理及操作各种数据库对象,如表、索引、视图等。本文将详细介绍Oracle的基本建表语句及相关操作。 首先,创建用户是数据库管理的基础...

    oracle数据库查询语句大全

    此外,文章还介绍了基本的Oracle操作语句,包括创建表、修改列属性、添加列、添加主键约束和非空约束、删除主键约束、失效约束、有效约束等。这些语句对于初学者非常重要,因为它们可以帮助初学者快速地掌握Oracle...

    用log4net向oracle写入数据

    Constraint PK_Log PRIMARY KEY (Id) ); ``` #### 四、配置Log4Net 接下来,配置Log4Net以便将日志信息发送到Oracle数据库中。这涉及到修改应用程序的配置文件(app.config/web.config),并添加适当的Appender。 ...

    oracle查询语句大全

    - **修改列属性**: - `ALTER TABLE 表名 MODIFY (列名 类型);` - **添加列**: - `ALTER TABLE 表名 ADD (列名 类型);` - **添加主键约束和非空约束**: - `ALTER TABLE 表名 ADD CONSTRAINT pk_表名 PRIMARY KEY ...

    Oracle系统表查询

    CREATE TABLE tablename (column1 data type [constraint], column2 data type [constraint], column3 data type [constraint]); 在创建新表格时,在关键词CREATE TABLE后面加入所要建立的表格的名称,然后在括号内...

    oracle中sql语句用法

    修改已存在的列的属性。 示例: ```sql ALTER TABLE student MODIFY name VARCHAR2(20); ``` ### 三、解锁Oracle用户 当Oracle用户被锁定时,需要解锁才能正常使用。 #### 解锁用户的语法示例 ```sql ALTER USER...

    从mysql转换到oracle数据库.docx

    6. 去掉 `auto_increment` 选择项,因为 Oracle 数据库中没有自增属性。 7. 将 `NOT NULL default ''` 替换成 `default '' NOT NULL`。 8. 将 `ON DELETE CASCADE ON UPDATE CASCADE` 替换成 `ON DELETE CASCADE ON ...

    powerdesigner创建oracle_数据库表,设置表主键列为自动增长

    CONSTRAINT PK_G_EC_LOTTERYCATEGORY PRIMARY KEY (lcId) ) / CREATE SEQUENCE Sequence_LotCateID INCREMENT BY 1 START WITH 1 NO MAXVALUE MINVALUE 1 NO CACHE; CREATE TRIGGER tib_g_ec_lotterycategory ...

    Oracle_基本建表语句.doc

    Oracle数据库是世界上最流行的数据库管理系统之一,广泛用于企业级的数据存储和管理。本文将详细解析Oracle的基本建表语句以及相关的数据库操作。 首先,创建用户是数据库管理的第一步。以下是一个创建用户`han`的...

    Oracle基本建表语句

    修改列的属性** **语法:** ```sql ALTER TABLE <table_name> MODIFY <column_name> ; ``` **示例:** ```sql ALTER TABLE test MODIFY address VARCHAR2(40); ``` #### 四、其他常用操作 **1. 创建自增序列** **...

    第二部分 ORACLE 数据对象的分析1(表_约束_视图)

    表是数据库中最基础的数据存储结构,它由一系列行和列组成,每行代表一条记录,每列代表一个属性。在Oracle中,创建表的基本语法如下: ```sql CREATE TABLE tb_name ( col1 datatype constraint, col2 datatype ...

    ocl.rar_ocl_oracle_oracle OCL_oracle OCL

    在Oracle的世界中,OCL(Object Constraint Language)是一种形式化语言,用于在模型驱动工程(MDE)中定义对象的行为和约束。OCL提供了一种标准化的方式来表达对UML(统一建模语言)模型的精确条件和限制,增强了...

Global site tag (gtag.js) - Google Analytics