Oracle基础 之 约束条件状态

以下是CONCEPT的文档描述：

ConstraintStates

■ENABLE ensures that all incoming data conforms to theconstraint
■ DISABLE allows incoming data, regardless of whether it conformsto the constraint
■ VALIDATE ensures that existing data conforms to theconstraint
■ NOVALIDATE means that some existing data may not conform to theconstraint

In addition:
■ ENABLE VALIDATE is the same as ENABLE. The constraint is checkedand is guaranteed to hold for all rows.
■ ENABLE NOVALIDATE means that the constraint is checked, but itdoes not have to be true for all rows. This allows existing rows toviolate the constraint, while ensuring that all new or modifiedrows are valid.

In an ALTER TABLE statement, ENABLE NOVALIDATE resumesconstraint checking on disabled constraints without firstvalidating all data in the table.
■ DISABLE NOVALIDATE is the same as DISABLE. The constraint is notchecked and is not necessarily true.
■ DISABLE VALIDATE disables the constraint, drops the index on theconstraint, and disallows any modification of the constrainedcolumns.

For a UNIQUE constraint, the DISABLE VALIDATE state enables youto load data efficiently from a nonpartitioned table into apartitioned table using the EXCHANGE PARTITION clause of the ALTERTABLE statement.

Transitions between these states are governed by the followingrules:
■ ENABLE implies VALIDATE, unless NOVALIDATE is specified.
■ DISABLE implies NOVALIDATE, unless VALIDATE is specified.
■ VALIDATE and NOVALIDATE do not have any default implications forthe ENABLE and DISABLE states.
■ When a unique or primary key moves from the DISABLE state to theENABLE state, if there is no existing index, a unique index isautomatically created. Similarly, when a unique or primary keymoves from ENABLE to DISABLE and it is enabled with a unique index,the unique index is dropped.
■ When any constraint is moved from the NOVALIDATE state to theVALIDATE state,all data must be checked. (This can be very slow.)However, moving from VALIDATE to NOVALIDATE simply forgets that thedata was ever checked.
■ Moving a single constraint from the ENABLE NOVALIDATE state tothe ENABLE VALIDATE state does not block reads, writes, or otherDDL statements. It can be
done in parallel.

 

以下摘自博客内容：

 

Oracle 中为了更好地处理需要数据暂时违反约束条件的情况，可将约束条件指定为不同的状态。

主要的约束条件状态有以下几类：

1、 DISABLE NOVALIDATE
2、DISABLE VALIDATE 
3、 ENABLE NOVALIDATE
4、ENABLE VALIDATE

这里的ENABLE/DISABLE的语义是：“启用 (ENABLE) 或禁用 (DISABLE) 完整性约束条件”。

如果启用约束条件，在数据库中输入或更新数据时就会检查数据。此时，禁止输入不符合约束条件规则的数据。如果禁用约束条件，则可以在数据库中输入不符合规则的数据。

所以可以理解ENABLE/DISABLE是对插入或修改的数据进行完整性约束条件检查的开关，主要针对新进入的数据；

 

VALIDATE/NOVALIDATE 主要针对现有数据的完整性约束检查；

 

下面通过小例子体会一下其中的差异：

SQL> create table test(id int,name varchar2(10));

Table created