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