An embarrassing " feature" in Oracle RDBMS (part 1)

A couple of months ago, while I was playing around with loading data, I found out that it's actually possible to break the referential integrity of the database. And this without leaving any traces behind. Check this example

-- create a simple PK-FK relationship
CREATE TABLE parent_tab 
(parent_id NUMBER NOT NULL); 
    
ALTER TABLE parent_tab 
ADD CONSTRAINT parent_pk PRIMARY KEY (parent_id);

CREATE TABLE child_tab 
(child_id NUMBER NOT NULL, 
 parent_id NUMBER NOT NULL);
 
ALTER TABLE child_tab 
ADD CONSTRAINT child_pk PRIMARY KEY (child_id);

ALTER TABLE CHILD_TAB
ADD CONSTRAINT child_fk FOREIGN KEY (parent_id) 
REFERENCES parent_tab (parent_id);

-- Insert some values
INSERT INTO parent_tab VALUES (1);
INSERT INTO parent_tab VALUES (2);
INSERT INTO child_tab VALUES (1,1);
INSERT INTO child_tab VALUES (2,1);
INSERT INTO child_tab VALUES (3,2);
INSERT INTO child_tab VALUES (4,2);

-- Modify the FK-Constraint
ALTER TABLE child_tab 
MODIFY CONSTRAINT child_fk DISABLE VALIDATE;

We now have a "DISABLE VALIDATE" constraint.  Such a constraint does not allow any DML on  it's table. This makes sense cause how could the database ever guarantee the constraint  (keep it valid) + allow DML and at the same time not check if DML-changes confirms to the constraint. (Note: So in fact we now have a READ-ONLY table.  That feature does not exist in pre 11g. So maybe this is way simulating that feature in earlier versions of Oracle. But that's  Blog-entry for the future).  BUT the parent table will allow DML-changes as all it's constraints are still ENABLED VALDIATED.

By deleting some records from the parent table and  re-enabling the FK-constraint, it's actually possible to create some orphans.

-- Delete records from parent_tab
DELETE FROM parent_tab 
 WHERE parent_id = 1;

COMMIT;

-- Now Enable the FK-Constraint again
ALTER TABLE child_tab 
MODIFY CONSTRAINT child_fk ENABLE VALIDATE;

-- Check the status of the constraint
SELECT constraint_name, status, validated
  FROM user_constraints
 WHERE table_name = 'CHILD_TAB'
   AND constraint_type= 'R';

-- Check data and the status of the constraint
SELECT parent_id FROM parent_tab;
 
 PARENT_ID
----------
         2
 
SELECT parent_id FROM child_tab;
 
 PARENT_ID
----------
         1
         1
         2
         2
 
SELECT constraint_name, status, validated
  FROM user_constraints
 WHERE table_name = 'CHILD_TAB'
   AND constraint_type= 'R';
 
CONSTRAINT_NAME                STATUS   VALIDATED
------------------------------ -------- -------------
CHILD_FK                       ENABLED  VALIDATED
 

All of a sudden we've broken the referential integrity of the database. Sounds like serious stuff to me.  Once I found this out I posted the above example in some Oracle forums. The reaction I got was mixed. Some people said it was a feature needed for loading huge amount of data in a DW-environment, some called it a Bug, but none really seemed to consider it more than annoying.   I also learned that this "feature" in actually in all versions from 8i-11g. I then raised an SR with Oracle and after some convincing I managed to get them to create an enhancement  request (They wouldn't of course call it a bug). But I still couldn't help being more than annoyed over this feature.

Think of the consequences:

1. As a DBA I can never ever be 100% sure that my constraints actually are valid.



2. I believe  such a "feature" can cause Oracle to return the wrong result.



3. One of the main jobs of a database is to guarantee referential integrity. This "feature" basically mean that Oracle can't do it. And hasn't been able to do it since 8i. I don't like to see Oracle being added to the list of databases that can't handle referential integrity.

On the other hand how can such a simple "feature" be present in version after version without anyone reporting it as a bug and without being caught by Oracles internal testing. So maybe after all this there is reason for this feature. Though I find it hard to believe.