Finding an Oracle Foreign Key by its ID to understand which tables are referenced by it

It’s quite common to receive errors when trying to executed SQL insert and delete because some of the changes we’re executing, cause some Oracle foreign keys to be violated.

I don’t like that the error message given by Oracle is not very clear about what’s exactly the cause of the problem, it just says something like this:

Error report:
SQL Error: ORA-02292: integrity constraint (MYDB.FKBA1C59B51590B46E) violated - child record found
02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
*Cause:    attempted to delete a parent key value that had a foreign
           dependency.
*Action:   delete dependencies first then parent or disable constraint.

In this message, I just know that the violated key name is FKBA1C59B51590B46E, in this case I usually need to write some more SQL to update (or delete) also the table where there are some dependencies.

Of course it’s useful to know which is that table. The command to execute is very simple, and I hardly understand why Oracle doesn’t show directly the table name where the foreign key is violated.

To know more about that cryptic FKBA1C59B51590B46E, just execute this:

select * from ALL_CONSTRAINTS where constraint_name = 'FKBA1C59B51590B46E';

Leave a Reply

Your email address will not be published. Required fields are marked *