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

SQL> alter table test add constraint ck_id check(id>10);

Table altered

DISABLE NOVALIDATE：这个很简单，就是对现有的数据和新插入的数据都不做完整性约束检查；

SQL> alter table test disable novalidate constraint ck_id;

Table altered

SQL> insert into test values(2,'2');

1 row inserted

DISABLE VALIDATE：对新数据不做验证，对现有数据做完整性约束检查。这样会有什么后果呢？因为在验证现有数据后又允许将未经检查的数据输入表中，会出现不一致的情况。因此当条件状态设为这一条件时，实际的结果就是：不允许对受约束的列进行任何修改

SQL> alter table test disable validate constraint ck_id;

alter table test disable validate constraint ck_id

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

SQL> delete from test where id<10;

1 row deleted

SQL> alter table test disable validate constraint ck_id;

Table altered

SQL> insert into test values(2,'2');

insert into test values(2,'2')

ORA-25128: 不能对带有禁用和验证约束条件 (XIEKELI.CK_ID) 的表进行插入/更新/删除

看看有什么后果，首先对现有的数据进行约束检查，因为存在id=2的这条记录，所以提示“ORA-02293: 无法验证 (XIEKELI.CK_ID) - 违反检查约束条件”

将非法的记录删除后，约束条件状态设置成功，此时对表进行插入后提示“ORA-25128: 不能对带有禁用和验证约束条件 (XIEKELI.CK_ID)的表进行插入/更新/删除”

ENABLENOVALIDATE：对现有的数据不做约束检查，对新数据进行约束检查。在可以更正现有约束条件违规情况，同时又不允许将新的违规数据输入到系统中时，常常会使用此状态。

先加一条违反约束的记录：

SQL> alter table test disable novalidate constraint ck_id;

Table altered

SQL> insert into test values(2,'2');

1 row inserted

设置约束条件状态后，看看：

SQL> alter table test enable novalidate constraint ck_id;

Table altered

SQL> insert into test values(3,'3');

insert into test values(3,'3')

ORA-02290: 违反检查约束条件 (XIEKELI.CK_ID)

SQL> insert into test values(13,'13');

1 row inserted

 

没错，只对新数据进行了检查，原来加入到那条（2，‘2’），被忽略了。

 

ENABLEVALIDATE：新数据与现有数据均符合约束条件。这是约束条件的典型状态和默认状态。

 

通过这张图可能看得更清晰：