Enable/Disable/Validate/Novalidate几个关键字对Constraint的控制

Enable/Disable/Validate/Novalidate几个关键字对Constraint的控制（转载）

 

一、组合特性说明：   是否要求满足约束    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相同，禁用约束，删除约束上的索引，且允许修改被约束的记录。 Alter table table_name Enable/Disable [Validate/Novalidate] constraint constraint_name; Alter table table_name Modify constraint constraint_name Enable/Disable/Validate/Novalidate/ Enable Validate/Enable Novalidate/Disable Validate/Disable Novalidate; Alter table table_name                       -----错误语法 Validate/Novalidate constraint constraint_name;   -----Alter table不能直接指定Validate/Novalidate 注：以上为Check约束语法，不同的约束类型语法不尽相同，请自行分析。 二、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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/611609/viewspace-580405/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/611609/viewspace-580405/