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

约束操作详解

最新推荐文章于 2023-08-23 11:14:27 发布

转载最新推荐文章于 2023-08-23 11:14:27 发布 · 251 阅读

文章标签：

#数据库

本文详细解析了数据库中约束的Enable、Disable、Validate与Novalidate等关键字的功能及使用场景，并通过实例展示了不同组合对数据校验的影响。

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/