Wednesday, October 03, 2007

More on DISABLE VALIDATE

Yesterday I blogged about a "bug" with relational integrity. Due to lack of time I didn't really have time to explain why this behaviour occurs.

When we disable the constraint with the proper syntax the constraint is marked as NOT VALIDATED:

SQL> ALTER TABLE temp_child DISABLE CONSTRAINT what_the_fk;

Table altered.

SQL> SELECT status, validated FROM user_constraints u
2 WHERE table_name= 'TEMP_CHILD'
3 AND u.constraint_type ='R';
STATUS VALIDATED
-------- -------------
DISABLED NOT VALIDATED

SQL>

Whereas, when we disable it the wrong way this doesn't happen:

SQL> ALTER TABLE temp_child DISABLE VALIDATE CONSTRAINT what_the_fk;

Table altered.

SQL> SELECT status, validated FROM user_constraints u
2 WHERE table_name= 'TEMP_CHILD'
3 AND u.constraint_type ='R';
STATUS VALIDATED
-------- -------------
DISABLED VALIDATED

SQL>

The bug occurs because the database trusts the value of VALIDATED rather than actually validating the constraint. So I thought I should see whether it applies to other kinds of constraints. This is what happens with a check constraint:

SQL> create table nn (col1 number)
2 /

Table created.

SQL> alter table nn add constraint nn_ck check (col1 is not null)
2 /

Table altered.

SQL> insert into nn values (null)
2 /
insert into nn values (null)
*
ERROR at line 1:
ORA-02290: check constraint (APC.NN_CK) violated


SQL> alter table nn disable validate constraint nn_ck
2 /

Table altered.

SQL> insert into nn values (null)
2 /
insert into nn values (null)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (APC.NN_CK)
disabled and validated


SQL>

And this with a unique key:

SQL> create table my_nu_tab (col1 number)
2 /

Table created.

SQL> alter table my_nu_tab add constraint nu_uk unique (col1)
2 /

Table altered.

SQL> insert into my_nu_tab values (1)
2 /

1 row created.

SQL> insert into my_nu_tab values (1)
2 /
insert into my_nu_tab values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (APC.NU_UK) violated


SQL> alter table my_nu_tab disable validate constraint nu_uk
2 /

Table altered.

SQL> insert into my_nu_tab values (1)
2 /
insert into my_nu_tab values (1)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (APC.NU_UK)
disabled and validated


SQL>

Interesting. So what happens when we try to insert into the child table when the foreign key is disabled yet validated?

SQL> insert into temp_child values (2, 11)
2 /
insert into temp_child values (2, 11)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (APC.WHAT_THE_FK)
disabled and validated


SQL>

So at least the database prevents us from inserting orphaned records into the referencing table. It just doesn't protect the integrity of the referenced table. Which is fair enough I suppose - the foreign key is disabled after all.

I'm trying to think of a scenario in which we would want to disable a constraint but still keep it validated but so far nothing has occurred to me. Obviously there must be a good reason for this. Any suggestions?

Labels: ,

5 Comments:

Blogger jonas said...

I can understand how this "feature" is used during partition exchange in a DW-environment. But then as I understand it it's used on top of PK,UK and Check constraint.
And as these constraints does not reference any other tables + The fact that a DISABLE VALIDATED constraint protects that table from any DML =>
means the reference integrity is never in danger.
So no problem there!

But as soon as we do this on FK constraint we put the reference integrity in danger.
And I can't really see why even a partition exchange or any other feature ever has to do this.

Then now think of the consequences:
-It means that we can never ever trust the information in USER_CONSTRAINTS. How can we now know if we need to revalidate the constraints or nor
-There is no way to know if our database actually is referentially correct => Serious -Stuff
-My understanding was also that the optimizer uses this information when it optimizes a query. I guess that's wrong then or this "feature" could cause wrong data, which clearly would be a bug. I always been told that the optimizer is aware of FKs:s. But the more I work with Oracle the more I believe that's not the case. (Guess that question is another thread)


So the more I think about this the more I believe it's a bug.
I expected the FK to become NOT VALIDATED as soon as I delete touched the parent table.
OR
that DISABLE VALIDATE wouldn't be a valid option for FK:s.
/wilhelm2000

4 October 2007 01:00:00 GMT-7  
Anonymous Anonymous said...

Is it really so much a bug or a manifestation that user_constraints is an either/or proposition for the entire constraint, while a partition exchange is a physical event that places a part of the constraint into an undefined state?

4 October 2007 16:09:00 GMT-7  
Anonymous Anonymous said...

We utilize this 'bug' when doing refreshes of a dev environment. Let's say you want a complete schema refresh, but there are a few large tables that aren't needed. Truncate the dev tables in question then place a check constraint on them:

alter table tabname add constraint tabname_read_only check (1=1)disable validate;

This prevents the import from populating these tables. Much easier then developing a long TABLES list of the desired tables in the imp parfile.

5 August 2008 10:41:00 GMT-7  
Blogger laptop battery said...

toshiba pa3154u-1brs battery
toshiba portege 2000 battery
toshiba portege r100 battery
toshiba pa3098u battery
toshiba satellite 1200 battery
toshiba satellite 3000 battery
toshiba pa3331u battery
toshiba satellite m30 battery
toshiba satellite m35 battery

toshiba pa3009ur-1bar battery
toshiba tecra 8100 battery
toshiba pa3465u-1brs battery
toshiba pa3399u battery
toshiba satellite m40 battery
toshiba satellite m45 battery
toshiba satellite m50 battery
toshiba satellite m55 battery
toshiba pa3166u-1bas battery

toshiba satellite 1900 battery
toshiba satellite 1905 battery
toshiba pa3383u-1brs battery
toshiba pa3383u battery
toshiba satellite a70 battery
toshiba satellite p30 battery
toshiba satellite p35 battery
toshiba pa3382u-1bas battery
toshiba pa3384u-1bas battery

toshiba satellite a60 battery
toshiba satellite a65 battery
toshiba pa33842u-1brs battery
toshiba pa3384u-1bas battery
toshiba pa3356u-1brs battery
toshiba portege m300 battery
toshiba portege m500 battery
toshiba pa3128u battery
toshiba pa3191u battery

toshiba portege m200 battery
toshiba portege m205 battery
toshiba pa3123-1bas battery
toshiba satellite 5000 battery
toshiba pa3291u battery
toshiba satellite p20 battery
toshiba satellite p25 battery
toshiba pa2487ur battery
toshiba pa2487u battery

toshiba pa3250u battery
toshiba satellite 2430 battery
toshiba satellite a30 battery
toshiba pa3356u-1bas battery
toshiba satellite a50 battery
toshiba satellite a55 battery
toshiba pa3399u-1bas battery
toshiba satellite a100 battery
toshiba satellite m100 battery

30 September 2008 02:42:00 GMT-7  
Anonymous Anonymous said...

情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣,情趣,情趣,情趣,情人歡愉用品,情趣用品,AIO交友愛情館,情人歡愉用品,美女視訊,情色交友,視訊交友,辣妹視訊,美女交友,嘟嘟成人網,按摩棒,震動按摩棒,微調按摩棒,情趣按摩棒,逼真按摩棒,G點,跳蛋,跳蛋,跳蛋,性感內衣,飛機杯,充氣娃娃,情趣娃娃,角色扮演,性感睡衣,SM,潤滑液,威而柔,香水,精油,芳香精油,自慰,自慰套,性感吊帶襪,情趣用品加盟,情人節禮物,情人節,吊帶襪,成人網站,AIO交友愛情館,情色,情色貼圖,情色文學,情色交友,色情聊天室,色情小說,七夕情人節,色情,A片,A片下載,免費A片,免費A片下載,情色電影,色情網站,辣妹視訊,視訊聊天室,情色視訊,免費視訊聊天,視訊聊天,美女視訊,視訊美女,美女交友,美女,情色交友,成人交友,自拍,本土自拍,情人視訊網,視訊交友90739,生日禮物,情色論壇,正妹牆,正妹,成人網站,A片,免費A片,A片下載,免費A片下載,AV女優,成人影片,色情A片,成人論壇,情趣,免費成人影片,成人電影,成人影城,愛情公寓,色情影片,保險套,舊情人,微風成人,成人,成人遊戲,成人光碟,色情遊戲,跳蛋,按摩棒,一夜情,男同志聊天室,肛交,口交,性交,援交,免費視訊交友,視訊交友,一葉情貼圖片區,性愛,視訊,嘟嘟成人網

愛情公寓,情色,舊情人,情色貼圖,情色文學,情色交友,色情聊天室,色情小說,一葉情貼圖片區,情色小說,色情,色情遊戲,情色視訊,情色電影,aio交友愛情館,色情a片,一夜情,辣妹視訊,視訊聊天室,免費視訊聊天,免費視訊,視訊,視訊美女,美女視訊,視訊交友,視訊聊天,免費視訊聊天室,情人視訊網,影音視訊聊天室,視訊交友90739,成人影片,成人交友,美女交友,微風成人,嘟嘟成人網,成人貼圖,成人電影,A片

11 October 2008 10:18:00 GMT-7  

Post a Comment

<< Home