I want to delete a record, but sometimes 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 like to use a query that checks if there are dependencies of this record, so that I would then be or not for the delete query.
Is there any way to get this response through a query? How would it look?
This is what you want to do, but it would cost more to the database than you try to delete the direct record. I'll show you why.
First you have to rely 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 get each table and the fields and make a select to see if it has the data. Imagine a large 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 handle it within the system. I use the default:
TABLE_PK TABELA_FKXX
So I know the name of the problem table and I'll look for the XX index to see what the reference is and what the problem is.
I hope I have helped.