Oracle Challenge

Today I ran into a strange Oracle situation. It took me some time to realize what it is, so I decided to write it as a challenge. Hope you will find it interesting.

I have 2 tables, A and B. Each table has indexes, constraints, many columns and more (But no triggers). Table A has the ID column (which is the primary key) and the NAME column (varchar2). Table B has the ID column (primary key) and the A_ID column (which is FK to table A).

What I tried to do is to delete a specific row from A:


SQL> delete from a where name='x';
delete from a where name='x'
*
ERROR at line 1:
ORA-02292: integrity constraint (LIRON.B_FK) violated - child record found

As you can see I got the constraint violation error, which means there are child records in B, so I checked:


SQL>  select * from b
where a_id in (select id from a where name='x');

no rows selected

But there are no rows!
What’s going on here?
I will post the answer in a few days.
Good luck

Advertisements

11 thoughts on “Oracle Challenge

  1. Hi,

    maybe because a VPD policy is defined on the child table b? E.g. if we take a setup like the one defined here http://www.adp-gmbh.ch/ora/security/vpd/ (and drop the FK from the employee table to simplify the test case), then:

    SQL> connect frank/frank
    Connected.

    SQL> delete from test.department where name = ‘Sales’;
    delete from test.department where name = ‘Sales’
    *
    ERROR at line 1:
    ORA-02292: integrity constraint (TEST.SYS_C009998) violated – child record found

    SQL> select * from test.department_secrets
    2 where dep_id in (select dep_id from test.department where name = ‘Sales’);

    no rows selected

    Regards,
    Jure

    Like

      • What about having invalid data in partitions, i.e. having data with a partition key that doesn’t belong to a certain partition and using an execution plan which allows partition pruning:

        SQL> create table a(id number primary key, name varchar2(10));
        
        SQL> create table b(id number primary key, id_a number)
          2  partition by range (id_a)
          3  (PARTITION p1 VALUES LESS THAN (2),
          4   PARTITION p2 VALUES LESS THAN (3));
        
        SQL> create table b_tmp(id number primary key, id_a number);
        
        SQL> insert into a values (1, 'x');
        SQL> insert into b_tmp values (2, 1);
        
        SQL> create index i_b on b(id_a) global;
        
        SQL> alter table b exchange partition p2 with table b_tmp without validation update global indexes;
        
        SQL> alter table b add constraint fk_a foreign key (id_a) references a(id) enable novalidate;
        
        SQL> delete from a where name='x';
        delete from a where name='x'
        *
        ERROR at line 1:
        ORA-02292: integrity constraint (SCOTT.FK_A) violated - child record found
        
        SQL> select /*+full(b)*/ * from b
          2  where id_a in (select /*+full(a)*/ id from a where name='x');
        
        no rows selected
        
        SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last +partition'));
        
        Plan hash value: 1876729957
        
        -------------------------------------------------------------------------------------
        | Id  | Operation                 | Name | Starts | E-Rows | Pstart| Pstop | A-Rows |
        -------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT          |      |      1 |        |       |       |      0 |
        |   1 |  NESTED LOOPS             |      |      1 |      1 |       |       |      0 |
        |*  2 |   TABLE ACCESS FULL       | A    |      1 |      1 |       |       |      1 |
        |   3 |   PARTITION RANGE ITERATOR|      |      1 |      1 |   KEY |   KEY |      0 |
        |*  4 |    TABLE ACCESS FULL      | B    |      1 |      1 |   KEY |   KEY |      0 |
        -------------------------------------------------------------------------------------
        
        Predicate Information (identified by operation id):
        ---------------------------------------------------
        
           2 - filter("NAME"='x')
           4 - filter("ID_A"="ID")
        

        However, if partition pruning doesn’t occur as in the previous example, we get a different answer, since Oracle visits the table B by first obtaining the rowid from the global index on id_a:

           
        SQL> select * from b
          2  where id_a in (select id from a where name='x');
        
                ID       ID_A
        ---------- ----------
                 2          1
        
        SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last +partition'));
        		 
        Plan hash value: 3220910207
        
        -----------------------------------------------------------------------------------------------
        | Id  | Operation                           | Name | Starts | E-Rows | Pstart| Pstop | A-Rows |
        -----------------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT                    |      |      1 |        |       |       |      1 |
        |   1 |  NESTED LOOPS                       |      |      1 |      1 |       |       |      1 |
        |   2 |   NESTED LOOPS                      |      |      1 |      1 |       |       |      1 |
        |*  3 |    TABLE ACCESS FULL                | A    |      1 |      1 |       |       |      1 |
        |*  4 |    INDEX RANGE SCAN                 | I_B  |      1 |      1 |       |       |      1 |
        |   5 |   TABLE ACCESS BY GLOBAL INDEX ROWID| B    |      1 |      1 | ROWID | ROWID |      1 |
        -----------------------------------------------------------------------------------------------
        
        Predicate Information (identified by operation id):
        ---------------------------------------------------
        
           3 - filter("NAME"='x')
           4 - access("ID_A"="ID")
        
        

        Regards,
        Jure

        Like

  2. Something like this (please edit if the formatting tags don’t work):

    
    -- starting with empty tables:
    
    SQL> insert into a values (1,1,'A');
    
    1 row created.
    
    SQL> insert into a values (2,2,'x');
    
    1 row created.
    
    SQL> insert into b values (1,2,'padding');
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> delete from a where name = 'x';
    delete from a where name = 'x'
    *
    ERROR at line 1:
    ORA-02292: integrity constraint (TEST_USER.SYS_C0048745) violated - child record found
    
    
    SQL> select * from b where a_id in (select id from a where name = 'x');
    
    no rows selected
    
    

    Like

    • Hi Jonathan,
      I couldn’t reproduce it, what is the structure of the tables?
      This is what I tried:

      SQL> create table a (id number primary key, id1 number, name varchar2(10));
      
      Table created.
      
      SQL> create table b(id number primary key, a_id number, name varchar2(10));
      
      Table created.
      
      SQL> alter table b add constraint b_fk foreign key (a_id) references a;
      
      Table altered.
      
      SQL> insert into a values (1,1,'A');
      
      1 row created.
      
      SQL> insert into a values (2,2,'x');
      
      1 row created.
      
      SQL> insert into b values (1,2,'padding');
      
      1 row created.
      
      SQL> commit;
      
      Commit complete.
      
      SQL> delete from a where name = 'x';
      delete from a where name = 'x'
      *
      ERROR at line 1:
      ORA-02292: integrity constraint (LIRON.B_FK) violated - child record found
      
      SQL> select * from b where a_id in (select id from a where name = 'x');
      
              ID       A_ID NAME
      ---------- ---------- ----------
               1          2 padding
      
      

      Like

      • Like yours, it’s extra constraints on that you might overlook.
        A has a PK on col1 and a UK on col2
        B has two FKs to A, col1 to col1 and col2 to col2. The SYS-generated constraint names helped to obscure this.

        Regards
        Jonathan Lewis

        Like

      • Oh, I see.
        Maybe I wasn’t clear enough, but I looked at the constraint name and checked which columns it is on before running the query from B looking for the missing child.

        Cheers,
        Liron

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s