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 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