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

5 Comments:

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

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?

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:

About Me

I studied history at university but didn't want to stay in academia. After my finals I read "Neuromancer" by William Gibson and decided to try computing. Those were the days when a history graduate with no relevant experience could get a job in IT. Luckily, I had an aptitude. At school, one teacher had repeatedly told my class that history was a training in the gathering, analysis and presentation of data; we scoffed but he was right. Also I enjoyed programming COBOL. It was like being paid to solve crossword puzzles all day.

Since 1992 I have worked on Oracle database systems. Over that time I have worked as a data modeller, designer, developer and DBA. I have also undertaken assignments as a business analyst and a technical architect. If pushed, I would have to say I'm happiest writing PL/SQL programs. I am least happy when attempting to marry a normalised data model to the needs of an ORM tool.

I was made an Oracle ACE programme in 2003 . In the same year I won the Oracle Magazine OTN Contributor of the Year award.