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
I would say: you forgot to alias the tables
Alex, I didn’t understand what you mean…
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
That’s a nice idea Jure. VPD can indeed cause this.
However, VPD was not in use in this case.
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
Something like this (please edit if the formatting tags don’t work):
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 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
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