Disable Validate

October 14, 2009 at 8:27 am (Oracle) (disable validate, Oracle Constraints, oracle disable validate)

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(meaning
the table becomes read only), but guarantees the validity of existing data.

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

SQL> drop table t;

Table dropped.

SQL> create table t(x number(1) constraint check_x unique, y number(1), z varchar2(1));

Table created.

SQL> insert into t values (1,1,’A');

1 row created.

SQL> insert into t values (2,2,’A');

1 row created.

SQL> select index_name,index_type, status from user_indexes where table_name=’T';

INDEX_NAME INDEX_TYPE STATUS
—————————— ————————— ——–
CHECK_X NORMAL VALID

SQL> select status, validated from user_constraints where constraint_name=’CHECK_X’;

STATUS VALIDATED
——– ————-
ENABLED VALIDATED

SQL> select * from t;

X Y Z
———- ———- -
1 1 A
2 2 A

SQL> update t set x=-2 where x=2;

1 row updated.

SQL> alter table t modify constraint check_x disable validate;

Table altered.

SQL> select index_name,index_type, status from user_indexes where table_name=’T';

no rows selected

SQL> select status, validated from user_constraints where constraint_name=’CHECK_X’;

STATUS VALIDATED
——– ————-
DISABLED VALIDATED

SQL> select * from t;

X Y Z
———- ———- -
1 1 A
-2 2 A

SQL> insert into t values (3,3,’C');
insert into t values (3,3,’C')
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.CHECK_X)
disabled and validated

SQL> update t set x=-1 where x=1;
update t set x=-1 where x=1
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.CHECK_X)
disabled and validated

SQL> delete from t where x=1;
delete from t where x=1
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.CHECK_X)
disabled and validated

SQL> select status, validated from user_constraints where constraint_name=’CHECK_X’;

STATUS VALIDATED
——– ————-
DISABLED NOT VALIDATED

SQL> insert into t values (4,4,’D');

1 row created.

SQL> select * from t;

X Y Z
———- ———- -
1 1 A
-2 2 A
4 4 D

SQL> update t set x=4 where x=-2;

1 row updated.

SQL> select * from t;

X Y Z
———- ———- -
1 1 A
4 2 A
4 4 D

SQL> alter table t modify constraint check_x disable validate;
alter table t modify constraint check_x disable validate
*
ERROR at line 1:
ORA-02299: cannot validate (SCOTT.CHECK_X) – duplicate keys found

SQL> update t set x=2 where x=4 and y=2;

1 row updated.

SQL> alter table t modify constraint check_x disable validate;

Table altered.

SQL> select status, validated from user_constraints where constraint_name=’CHECK_X’;

STATUS VALIDATED
——– ————-
DISABLED VALIDATED

SQL> update t set x=4 where x=2 and y=2;
update t set x=4 where x=2 and y=2
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.CHECK_X)
disabled and validated

SQL> select * from t;

X Y Z
———- ———- -
1 1 A
2 2 A
4 4 D

SQL> update t set y=3 where x=4;
update t set y=3 where x=4
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.CHECK_X)
disabled and validated

SQL> update t set z=’Z’ where x=4;
update t set z=’Z’ where x=4
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.CHECK_X)
disabled and validated