Re: Child records with no parent in 10.2.0.3

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!) Received on Wed Oct 03 2007 - 17:53:47 CDT