Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Child records with no parent in 10.2.0.3

Re: Child records with no parent in 10.2.0.3

From: <fitzjarrell_at_cox.net>
Date: Wed, 03 Oct 2007 09:15:45 -0700
Message-ID: <1191428145.540361.197860@w3g2000hsg.googlegroups.com>


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 Received on Wed Oct 03 2007 - 11:15:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US