How to check if my one-column record (FK) is present in other tables?

Question

I want to delete a record, but sometimes the oracle complains:

SQL error: ORA-02292: Integrity Constraint (SGR20_HOMOLOG.TB_CONFIGURACAO_EXERCICIO_T436) violated - Child record found 02292. 00000 - "Integrity Constraint (%s.%s) violated - Child record found"

I would then like to use a query that checks if there are dependencies of this record, so that then I go to the delete query or not.

Is there any way to get this answer through a query? What would it look like?

Answer 1

What you want to do is interesting but it would cost the database more than you trying to delete the record directly. I’ll show you why.

First you have to base yourself on this select:

SELECT a.table_name, a.column_name, a.constraint_name, c.owner, 
       c.r_owner, c_pk.table_name r_table_name, c_pk.constraint_name r_pk
  FROM all_cons_columns a
  JOIN all_constraints c ON a.owner = c.owner
                        AND a.constraint_name = c.constraint_name
  JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
                           AND c.r_constraint_name = c_pk.constraint_name
 WHERE c.constraint_type = 'R'
   AND a.table_name = :tabela

Then you have to take each table and those fields and make a select to see if you have the data. Imagine a big table with a few million records. Look how long this can take!!!

This is why it is easier for you to do the FK with a pattern and treat it within the system. I use the pattern:

TABELA_PK TABELA_FKXX

Because then I know the name of the table with problem and I will look for the Indice XX to see what the reference and what the problem is.

I hope I’ve helped.