Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Mudassar.

Asked: October 24, 2000 - 9:40 am UTC

Last updated: October 14, 2007 - 2:28 pm UTC

Version: 8.1.5

Viewed 1000+ times

You Asked

Can you explain the following extract from Oracle Education book for DBA8i. It says:

If you execute the ALTER TABLE .. ENABLE VALIDATE CONSTRAINT .. command when a constraint is enforced, it doesnot require any table locks during validation. The enforced constraint guarantees that no violations are introduced during validation. This has the following advantages:
1. All constraints are enabled concurrently
2. Each constraint is internally parallelized
3. Concurrent activity on the table is permitted

From the above extract, what I don't get is what is an ENFORCED constraint. Just for checking reasons I wrote the following:
ALTER TABLE TABLE_NAME ENFORCE CONSTRAINT CONSTRAINT_NAME;
and it worked. Then according to the above I tried to check the lock part, whether it actually locks the table or not, but infact it doesnot attempt to lock. Now, the first question is what is an ENFORCED constraint, and then how does this locking issue work with the enforced constraint. Lastly, if it does work how do the above advantages help us.

Thanks in advance,
Mudassar Shahzad

and Tom said...

Well, the enforce did not lock it. Enforce just says "from now on make sure everything is OK, ignore what is in there, but make sure newly inserted data is OK".

Here is an example. I create a table, I disable the constraint and simulate a bulk load. I loaded some data that will violate the constraint. I "enforce" the constraint (happens really fast) but do not validate it. This command works and in fact no new bad data may be entered -- however when we goto validate it -- the bad data is found and we cannot validate the constraint. This is typically used when the data you are bulk loading is known to be clean -- you would disable the constraints, load, and enforce the constraints (to speed the load). After the load, you enforce (but not validate necessarily) the constraint.

ops$tkyte@DEV816> create table t
2 ( x int,
3 constraint x_gt_zero check ( x > 0 )
4 )
5 /
create table t
*
ERROR at line 1:
ORA-00955: name is already used by an existing object


Elapsed: 00:00:00.00
ops$tkyte@DEV816>
ops$tkyte@DEV816> select status, validated
2 from user_constraints
3 where constraint_name = 'X_GT_ZERO';

STATUS VALIDATED
-------- -------------
ENABLED NOT VALIDATED

Elapsed: 00:00:00.02
ops$tkyte@DEV816>
ops$tkyte@DEV816> alter table t modify constraint x_gt_zero disable;

Table altered.

Elapsed: 00:00:00.10
ops$tkyte@DEV816> select status, validated
2 from user_constraints
3 where constraint_name = 'X_GT_ZERO';

STATUS VALIDATED
-------- -------------
DISABLED NOT VALIDATED

Elapsed: 00:00:00.01
ops$tkyte@DEV816>
ops$tkyte@DEV816> insert into t select object_id from all_objects;

23095 rows created.

Elapsed: 00:00:06.08
ops$tkyte@DEV816>
ops$tkyte@DEV816> insert into t values ( -1 );

1 row created.

Elapsed: 00:00:00.01
ops$tkyte@DEV816> alter table t modify constraint x_gt_zero enforce;

Table altered.

Elapsed: 00:00:00.08
ops$tkyte@DEV816> select status, validated
2 from user_constraints
3 where constraint_name = 'X_GT_ZERO';

STATUS VALIDATED
-------- -------------
ENABLED NOT VALIDATED

Elapsed: 00:00:00.01
ops$tkyte@DEV816>
ops$tkyte@DEV816> insert into t values ( -2 );
insert into t values ( -2 )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.X_GT_ZERO) violated


Elapsed: 00:00:00.02
ops$tkyte@DEV816> alter table t modify constraint x_gt_zero validate;
alter table t modify constraint x_gt_zero validate
*
ERROR at line 1:
ORA-02293: cannot validate (OPS$TKYTE.X_GT_ZERO) - check constraint violated


Elapsed: 00:00:00.06
ops$tkyte@DEV816>
ops$tkyte@DEV816> select status, validated
2 from user_constraints
3 where constraint_name = 'X_GT_ZERO';

STATUS VALIDATED
-------- -------------
ENABLED NOT VALIDATED

Elapsed: 00:00:00.01
ops$tkyte@DEV816>

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

disable validate

tp, July 19, 2004 - 3:32 am UTC

hi tom

could u tell me what is the use of disable validate constraint.can u give me a scenario where i can use this.
i am not able do any dml when the constraint is in disable validate state.

Tom Kyte
July 19, 2004 - 7:52 am UTC

metalink support note <Note:69637.1> has a good discussion of this.

The above metalink support not found

Arindam Mukherjee, August 21, 2006 - 2:35 am UTC

Respected Mr. Tom,

On altering constraint “Disable Validate”, table does now allow any DML statement irrespective of constrained column. In the Oracle 9i Data warehousing guide, chapter 7, the followings have been written.

************************************
However, there are trade-offs for the data warehouse administrator to consider with DISABLE VALIDATE constraints. Because this constraint is disabled, no DML statements that modify the unique column are permitted against the sales table. You can use one of two strategies for modifying this table in the presence of a constraint:

1. Use DDL to add data to this table (such as exchanging partitions). See the example in Chapter 14, "Maintaining the Data Warehouse".
2. Before modifying this table, drop the constraint. Then, make all necessary data modifications. Finally, re-create the disabled constraint. Re-creating the constraint is more efficient than re-creating an enabled constraint. However, this approach does not guarantee that data added to the sales table while the constraint has been dropped is unique.
************************************

What’s the practical usage of this constraint state if I drop and recreate as stated by point -2. If I don’t use this opportunity (DISABLE VALIDATE), where and what type of problem I am likely to face.


Tom Kyte
August 27, 2006 - 9:04 am UTC 

the practical usage is that while this constraint is in the disable validate mode - the table is for all intents and purposes "read only", "static", "known to be stable", "unmodifiable"

ops$tkyte%ORA10GR2> create table t ( x int, y int );

Table created.

ops$tkyte%ORA10GR2> insert into t values ( 1, 1 );

1 row created.

ops$tkyte%ORA10GR2> alter table t add constraint check_y check (y>0) disable validate;

Table altered.

ops$tkyte%ORA10GR2> update t set x = 2;
update t set x = 2
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint
(OPS$TKYTE.CHECK_Y) disabled and validated


If that is something you desire, go for it.

Disable Validate - Documentation is misleading

Anindya, October 13, 2007 - 11:25 am UTC

Hi Tom,
In the point #2 quoted (from documentation) by Arindam above, says - "However, this approach does not guarantee that
data added to the sales table while the constraint has been dropped is unique."

Well, the "approach" mentioned here is -- Drop constrait -> DML -> create the constraint back as Disable Validate.
I found that it definitely guarantees the uniqueness, else the constraint creation fails. So the concern raised by by documentation does not look like a valid one.

The metalink note (Note:69637.1) is correct when it says -- "This option disallows all DML on the table, but
guarantees the validity of existing data"
Tom Kyte
October 14, 2007 - 2:28 pm UTC

validate will validate, yes - not sure what you mean?

we were not talking about using disable VALIDATE.

ops$tkyte%ORA10GR2> create table t ( x int constraint check_x unique );

Table created.

ops$tkyte%ORA10GR2> insert into t values ( 1 );

1 row created.

ops$tkyte%ORA10GR2> insert into t values ( 1 );
insert into t values ( 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.CHECK_X) violated


ops$tkyte%ORA10GR2> alter table t drop constraint check_x;

Table altered.

ops$tkyte%ORA10GR2> insert into t values ( 1 );

1 row created.

ops$tkyte%ORA10GR2> alter table t add constraint check_x unique(x) disable;

Table altered.

ops$tkyte%ORA10GR2> select * from t;

         X
----------
         1
         1

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.