Child records with no parent in 10.2.0.3

From: <fitzjarrell_at_cox.net>
Date: Wed, 03 Oct 2007 08:31:12 -0700
Message-ID: <1191425472.890835.197140@r29g2000hsg.googlegroups.com>

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 Received on Wed Oct 03 2007 - 10:31:12 CDT