Oracle8i - DISABLE VALIDATE constraint state (文档 ID 69637.1)

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 then
load 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 the
unique 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 guarantees
the validity of all existing data in the table. 

Any violation of a DISABLE VALIDATE constraint will result in the following
error message:

ORA-25128: No insert/update/delete on table with 
           constraint (x.x) disabled and validated

Example
~~~~~~~

DISABLE VALIDATE constraint state is supported in the CREATE TABLE and also in
ALTER 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_indexes
	WHERE 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_indexes
	WHERE table_name = 'T1' ;

	Output: no rows selected