Oracle8i - DISABLE VALIDATE constraint state (文档 ID 69637.1)
来源:互联网 发布:阿里推荐算法负责人 编辑:程序博客网 时间:2024/06/03 02:44
Introduction~~~~~~~~~~~~Prior to Oracle 8i, integrity constraints could have the following state.1. ENABLE VALIDATE specifies that all old data also complies with the constraint along with the new ones. An enabled validated constraint guarantees that all data is and will continue to be valid. 2. ENABLE NOVALIDATE ensures that all new DML operations on the constrained data comply with the constraint, but does not ensure that existing data in the table complies with the constraint. 3. DISABLE means that Oracle ignores the constraint entirely. With Oracle 8i, DISABLE is now DISABLE NOVALIDATE and DISABLE VALIDATE.4. DISABLE NOVALIDATE is the same as DISABLE in the previous version, i.e. Oracle ignores the constraint entirely. 5. DISABLE VALIDATE disables the constraint and drops the index on the constraint, yet keeps the constraint valid. This clause is most useful for unique constraints. This option disallows all DML on the table, but guarantees the validity of existing data. Usage~~~~~This feature is most useful in data warehousing situations, where the need arises to load into a range-partitioned table a quantity of data with a distinct range of values in the unique key. In such situations, the disable validate state enables one to save space by not having an index. One can thenload data from a nonpartitioned table into a partitioned table using the "exchange_partition_clause" of the "ALTER TABLE" statement. This is at the expense of disallowing DML and index lookups.If the unique key coincides with the partitioning key of the partitioned table,disabling the constraint saves overhead and has no detrimental effects. If theunique key does not coincide with the partitioning key, Oracle performs automatic table scans during the exchange to validate the constraint, which might offset the benefit of loading without an index.Another benefit from the DISABLE VALIDATE constraint state is that it saves space because it requires no index on a unique or primary key, yet it guaranteesthe validity of all existing data in the table. Any violation of a DISABLE VALIDATE constraint will result in the followingerror message:ORA-25128: No insert/update/delete on table with constraint (x.x) disabled and validatedExample~~~~~~~DISABLE VALIDATE constraint state is supported in the CREATE TABLE and also inALTER TABLE statements. 1)CREATE TABLE t1 ( a NUMBER CONSTRAINT c1 PRIMARY KEY,b NUMBER );Output: Table created.2)SELECT constraint_name,validated,status FROM user_constraints WHERE table_name = 'T1' ;Output:CONSTRAINT_NAME VALIDATED STATUS------------------------------ ------------- --------C1 VALIDATED ENABLED 3)SELECT index_name , status FROM user_indexesWHERE table_name = 'T1' ;Output: INDEX_NAME STATUS------------------------------ --------C1 VALID 4)ALTER TABLE t1 MODIFY CONSTRAINT c1 DISABLE VALIDATE ; Output: Table altered. 5)SELECT constraint_name,validated,status FROM user_constraints WHERE table_name = 'T1' ;Output: CONSTRAINT_NAME VALIDATED STATUS------------------------------ ------------- --------C1 VALIDATED DISABLED 6)SELECT index_name , status FROM user_indexesWHERE table_name = 'T1' ;Output: no rows selected
0 0
- Oracle8i - DISABLE VALIDATE constraint state (文档 ID 69637.1)
- DISABLE CONSTRAINT
- oracle 9i 把表read only?oracle中的 CONSTRAINT 属性ENABLE DISABLE VALIDATE NOVALIDATE
- Enable/Disable/Validate/Novalidate
- enable validate/novalidate 以及 disable validate/novalidate
- 约束disable validate 状态测试
- 约束constraint的 enable/disable novalidate总结
- How to disable the scheduler using SCHEDULER_DISABLED attribute in 10g (文档 ID 1491941.1)
- How to disable the scheduler using SCHEDULER_DISABLED attribute in 10g (文档 ID 1491941.1)
- linux disable CPU c4/c6 state
- disable/enable validate/novalidate的区别
- disable/enable validate/novalidate 的区别
- disable/enable validate/novalidate 的区别 (ORACLE)
- disable/enable validate/novalidate 的区别
- 约束disable/enable validate/novalidate 的区别
- disable/enable validate/novalidate 的区别
- How To Validate A DataPump Export (EXPDP) Dump File ? (文档 ID 2113967.1) 转到底部 -------------------
- How to Disable Asynch_io on HP to Avoid Ioctl Async_config Error Errno = 1 (文档 ID 302801.1)
- tinyos--随机数
- mysql 库名 表名 大写
- UVa 10827 - Maximum sum on a torus
- 微信公众帐号开发教程第12篇-符号表情的发送(下)
- 海典ERP与汉码盘点机对接门店实时盘点计划及程序使用方法,实时盘点,云盘点方案
- Oracle8i - DISABLE VALIDATE constraint state (文档 ID 69637.1)
- DB2锁机制深度分析
- uafxcwd.lib(afxglobals.obj) : error LNK2001: 无法解析的外部符号 _IID_IWICImagingFactory
- Map和List两个集合类初学入门
- 股市基础知识、股票名词【三】
- 从零单排入门机器学习:Octave/matlab的常用知识之矩阵和向量
- 黑马程序员——java单例设计模式
- Android selector用法
- 分析函数