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?

4 comments:

jonas andersson 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

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?

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.

Anonymous 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