<fitzjarrell_at_cox.net> wrote in message
news:1191504334.777295.27360_at_57g2000hsv.googlegroups.com...
> On Oct 3, 5:53 pm, hjr.pyth..._at_gmail.com wrote:
>> On Oct 4, 2:15 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>>
>>
>>
>>
>>
>> > On Oct 3, 10:48 am, Carlos <miotromailcar..._at_netscape.net> wrote:
>>
>> > > fitzjarr..._at_cox.net ha escrito:
>>
>> > > > I was reading Howard J. Rogers' website today and noticed an
>> > > > interesting post. This was originally run on 10.2.0.2 on HP-UX
>> > > > Itanium, but it also presents itself on AIX 5.2 and 10.2.0.3 as
>> > > > illustrated:
>>
>> > > > SQL>
>> > > > SQL> --Create the tables and the constraint:
>> > > > SQL>
>> > > > SQL> CREATE TABLE temp_parent(parent_pk NUMBER NOT NULL,
>> > > > 2 constraint temp_parent_pk
>> > > > 3 primary key(parent_pk));
>>
>> > > > Table created.
>>
>> > > > SQL>
>> > > > SQL> CREATE TABLE temp_child(child_pk NUMBER NOT NULL, child_fk
>> > > > NUMBER
>> > > > NOT NULL,
>> > > > 2 constraint temp_child_pk
>> > > > 3 primary key (child_pk),
>> > > > 4 constraint temp_child_fk
>> > > > 5 foreign key (child_fk)
>> > > > 6 references temp_parent(parent_pk));
>>
>> > > > Table created.
>>
>> > > > SQL>
>> > > > SQL> -- Load data:
>> > > > SQL>
>> > > > SQL> INSERT INTO temp_parent VALUES(1);
>>
>> > > > 1 row created.
>>
>> > > > SQL>
>> > > > SQL> INSERT INTO temp_child
>> > > > 2 SELECT ROWNUM, 1 FROM all_objects;
>>
>> > > > 5812 rows created.
>>
>> > > > SQL>
>> > > > SQL> commit;
>>
>> > > > Commit complete.
>>
>> > > > SQL>
>> > > > SQL> -- Now disable the FK constraint VALIDATE
>> > > > SQL>
>> > > > SQL> ALTER TABLE temp_child DISABLE VALIDATE CONSTRAINT
>> > > > temp_child_fk;
>>
>> > > > Table altered.
>>
>> > > > SQL>
>> > > > SQL> -- Show constraint status
>> > > > SQL>
>> > > > SQL> SELECT status, validated FROM user_constraints u
>> > > > 2 WHERE table_name= 'TEMP_CHILD'AND u.constraint_type ='R';
>>
>> > > > STATUS
>> > > > VALIDATED
>> > > > --------
>> > > > -------------
>> > > > DISABLED
>> > > > VALIDATED
>>
>> > > > SQL>
>> > > > SQL> -- Delete from the parent
>> > > > SQL> --
>> > > > SQL> -- This shouldn't succeed since the constraint was disabled
>> > > > VALIDATE, but it does
>> > > > SQL>
>> > > > SQL> DELETE FROM temp_parent;
>>
>> > > > 1 row deleted.
>>
>> > > > SQL> commit;
>>
>> > > > Commit complete.
>>
>> > > > SQL>
>> > > > SQL> -- Retrieve constraint status
>> > > > SQL> --
>> > > > SQL> -- Should this now be 'NOT VALIDATED'?
>> > > > SQL>
>> > > > SQL> SELECT status, validated FROM user_constraints u
>> > > > 2 WHERE table_name= 'TEMP_CHILD'AND u.constraint_type ='R';
>>
>> > > > STATUS
>> > > > VALIDATED
>> > > > --------
>> > > > -------------
>> > > > DISABLED
>> > > > VALIDATED
>>
>> > > > SQL>
>> > > > SQL> -- This shouldn't succeed, period
>> > > > SQL> --
>> > > > SQL> -- It does
>> > > > SQL>
>> > > > SQL> ALTER TABLE temp_child ENABLE VALIDATE CONSTRAINT
>> > > > temp_child_fk;
>>
>> > > > Table altered.
>>
>> > > > SQL>
>> > > > SQL> -- Status claims it's validated, but how can it be without the
>> > > > parent record?
>> > > > SQL>
>> > > > SQL> SELECT status, validated FROM user_constraints u
>> > > > 2 WHERE table_name= 'TEMP_CHILD'AND u.constraint_type ='R';
>>
>> > > > STATUS
>> > > > VALIDATED
>> > > > --------
>> > > > -------------
>> > > > ENABLED
>> > > > VALIDATED
>>
>> > > > SQL>
>> > > > SQL> -- Drop tables
>> > > > SQL>
>> > > > SQL> drop table temp_child;
>>
>> > > > Table dropped.
>>
>> > > > SQL>
>> > > > SQL> drop table temp_parent;
>>
>> > > > Table dropped.
>>
>> > > > SQL>
>>
>> > > > What is the issue here? Is this a 'feature'? A 'bug'? Since the
>> > > > constraint was disabled VALIDATE am I correct in presuming the
>> > > > delete
>> > > > from the parent table shouldn't occur? I realise this is may be a
>> > > > contrived situation but I find it a concern nonetheless.
>>
>> > > > David Fitzjarrell
>>
>> > > David:
>>
>> > > Answered already at Dizwell... If the constraint is DISABLED
>> > > VALIDATE,
>> > > Oracle doesn't validate it when ENABLE VALIDATE (It must override the
>> > > validation since there is no change from 'VALIDATE' to 'VALIDATE':
>> > > the
>> > > validation is not fired).
>>
>> > > Anyway, it seems sort of a bug for me.
>>
>> > > Cheers.
>>
>> > > Carlos.- Hide quoted text -
>>
>> > > - Show quoted text -
>>
>> > What I don't understand is why the delete from the parent table is
>> > allowed since the constraint was disabled VALIDATE. One would expect
>> > the mechanism to notice that the child data would no longer be valid
>> > with the parent gone and disallow the delete. It doesn't.
>>
>> > David Fitzjarrell
>>
>> Because the constraint constrains the child table and only the child
>> table. DISABLE VALIDATE will effectively lock the child table from any
>> DML, thus ensuring no naughty child records get inserted or created by
>> updates. But the constraint is not constraining the parent table, is
>> it?
>>
>> I suppose in a nutshell: what table do you specify when you say 'alter
>> table disable validate constraint XXXX': it's **that** table that is
>> protected from bad DML. Any other table can do what it likes in the
>> meantime.
>>
>> And as Carlos explained, since the constraint is in the VALID state at
>> all times, putting it to ENABLE VALIDATE doesn't require us to check
>> the child table for validity: by definition, **it's** contents have
>> remained valid throughout.
>>
>> I wouldn't call it a bug. I would call it logically explicable but
>> rather unexpected behaviour (think quantum physics, which is similarly
>> bizarre but explicable!)- Hide quoted text -
>>
>> - Show quoted text -
>
> I believe Heisenberg had something else in mind with his Uncertainty
> Principle, but apparently it's also applicable here. It doesn't make
> much sense to me, in terms of the nature of the type of constraint in
> question (Foreign Key, referential integrity) but I suppose in some
> way it is 'logically explicable but rather unexpected', but it's also
> disconcerting when I can, though a series of successful actions,
> create a set of orphaned child records whilst the constraint, now
> enabled, blissfully ignores that the parent has now 'left the
> building'.
>
Hi David
This certainly appears to be a "bug" as the end result is undesirable, an
enabled validated constraint with possible violations.
Clearly, when a FK is in a disable validated state, the fact the constraint
is still validated should make all parent key delete/update operations
continue to check the validity of the operation on the dependant tables.
Then again, as the FK is disabled, Oracle clearly is not bothering leaving
this hole open.
However, putting a FK in disable validate is questionable to say the least?
The fact a FK can be invalidated from two directions (via operations on the
parent or the child table) has been missed here as Oracle is only enforcing
the validation from the one direction (child operations only).
I would have thought that either Oracle:
Continue to enforce the FK constraint from both directions (both parent and
child operations) if in a disable validate state (which would make such a
state redundant), or
Disallow disable validate operations on FKs as such a state is redundant
with FKs and leaves open this validation issue.
Then again, I'm neither an Oracle kernel developer nor a philosopher so what
would I know !!
Cheers
Richard
Received on Thu Oct 04 2007 - 08:57:59 CDT
