Enable/Disable/Validate/Novalidate

2007/06/25

Validate确保已有数据符合约束；

Novalidate不必考虑已有数据是否符合约束。

除非Novalidate被指定，Enable默认Validate；

除非Validate被指定，Disable默认Novalidate；

Validate和Novalidate对Enable和Disable没有任何默认暗示。

Enable Validate与Enable相同，检查已有记录和新增记录，确保都符合约束；

Enable Novalidate 允许已有记录不必满足约束条件，但新增/修改的记录必须满足；

Disable Validate禁用约束，删除约束上的索引，不允许修改任何被约束的记录；

Disable Novalidate与Disable相同，禁用约束，删除约束上的索引，且允许修改被约束的记录。

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.

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