Re: Child records with no parent in 10.2.0.3

On Oct 4, 12:53 am, 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!)

But: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. Received on Thu Oct 04 2007 - 07:02:41 CDT