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.