Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: June 07, 2003 - 6:26 pm UTC

Last updated: September 28, 2007 - 5:42 pm UTC

Version: 9.20

Viewed 10K+ times! This question is

You Asked

I have hard time understanding the usage of 'disable validate'. Could you help me understand its usefulness? Thanks.

and Tom said...

it lets a constraint be "disabled" but will prevent the modification of data while it is still in the validated mode:

ops$tkyte@ORA920> insert into t values ( 0 );
insert into t values ( 0 )
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (OPS$TKYTE.X_CHECK) disabled and validated

It's "usefulness" is limited to the case where

a) you have a read only set of data, like in a data warehouse
b) you are using partitioning, you load data by partition (eg: no dml in the other partitions)
c) you load a table with new data and use exchange partition to "load" the real table
d) some column(s) must be unique in the data, but the need or desire for an index on such a column(s) is not there. You would like to save the space of the index, yet be assured uniqueness.

In this case, the contraint will be "there", the exchange partition will scan the data and ensure the data is unique, but there won't be any persistent index. Hence the DW data is "clean", known to be "clean" but you don't have an index to enforce it.



Rating

  (5 ratings)

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

Comments

Thanks

Reader, June 08, 2003 - 12:28 pm UTC


How does oracle do it?

Reader, June 08, 2003 - 9:41 pm UTC

--> it lets a constraint be "disabled" but will prevent the modification of data while it is still in the validated mode.

Tom, could you explain how oracle does it when the constraint is in disabled state and also index is not there to enforce the validity of data? Which mechanism prevents DML? Thanks.


Tom Kyte
June 09, 2003 - 7:06 am UTC

ops$tkyte@ORA920> insert into t values ( 0 );
insert into t values ( 0 )
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (OPS$TKYTE.X_CHECK) 
disabled and validated

the database just says "no, you cannot do that"

that is how.  NO data, not even conforming data, can be added. 

A reader, June 09, 2003 - 11:51 am UTC


A reader, July 27, 2006 - 7:54 pm UTC


order of constraint check

Kishore, September 28, 2007 - 7:36 am UTC

Hello Sir,

Could you please let me know what order is the constraint check done in oracle. For ex I have a table as below

CREATE TABLE CONSTRAINT_SEQ
(
Col1 VARCHAr2(10)
,Col2 VARCHAr2(20)
,Col3 VARCHAR2(30)
);

ALTER TABLE CONSTRAINT_SEQ
ADD CONSTRAINT uk_col1_col2 UNIQUE (Col1,Col2);

ALTER TABLE CONSTRAINT_SEQ
ADD CONSTRAINT uk_col1_col2_col3 UNIQUE (Col3,COl2,Col1);

I know the fact that if Col1, Col2 combination has to be unique then combination of Col1, Col2, Col3 will always be unique, however I wanted to know if I insert two rows in the table with duplicate value for Col1, Col2 which constraint will be voilated first?

We have such a scenario in our system and in the DEV env constraint 1 fails and in UAT constraint 2.

1. Is it possible because of the storage of index ?
2. Will it be dependent on the index segment it tries to insert first ?
3. Will it be dependent on the order of creation of index?

Thanks & Regards
Kishore

Tom Kyte
September 28, 2007 - 5:42 pm UTC

it is not defined and you should not rely on any order of failure. We are free to evaluate them in any order we desire.


Even if any of 1,2, or 3 were true in your specific version, they might not be tomorrow.

So, you'll need to change your expectation - and expect just that Oracle will fail the insert when the unique constraint is violated.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.