ORACLE - child record not found

Question

I'm trying to delete one record from DB but ORACLE claims "integrity constraint violated - child record found".

If I try to find this child record, it is inexistent in DB.

Any one know how to solve this?

SQL> delete from AMZ_SERVCO where ID_SERVCO = 447;

delete from AMZ_SERVCO where ID_SERVCO = 447
*
ERROR at line 1:
ORA-02292: integrity constraint (AMAZONIA.FK_AMZ_FRNQIA_PA_AMZ_SERVCO) violated - child record found

SQL> select table_name from all_tab_columns where column_name='ID_SERVCO';

TABLE_NAME
------------------------------
AMZ_DESCTO_PO_SERVCO_APLCAO
AMZ_DESCTO_PO_SERVCO
BKP151207_AMZ_ARQVO_DESCTO
BKP_ARQVO_DESCTO
AMZ_SERVCO_PO
AMZ_SERVCO_PA
AMZ_SERVCO
AMZ_FRNQIA_PO_SERVCO
AMZ_DESCTO_PO
AMZ_CATEGR_PO
AMZ_CATEGR_PA

TABLE_NAME
------------------------------
AMZ_BONUS_PO
AMZ_ASSTUR_PO
AMZ_ASSTUR_PA
AMZ_ARQVO_SERVCO
AMZ_ARQVO_DESCTO
AMZ_APA_PO
AMZ_APA_PA
AMZ_FRNQIA_PA_SERVCO
AMZ_EXPSAO_ENTDDE_OPRDRA

20 rows selected.

SQL> select * from AMZ_EXPSAO_ENTDDE_OPRDRA WHERE ID_SERVCO = 447;
select * from AMZ_FRNQIA_PA_SERVCO WHERE ID_SERVCO = 447;
select * from AMZ_APA_PA WHERE ID_SERVCO = 447;
select * from AMZ_APA_PO WHERE ID_SERVCO = 447;
select * from AMZ_ARQVO_DESCTO WHERE ID_SERVCO = 447;
select * from AMZ_ARQVO_SERVCO WHERE ID_SERVCO = 447;
select * from AMZ_ASSTUR_PA WHERE ID_SERVCO = 447;
select * from AMZ_ASSTUR_PO WHERE ID_SERVCO = 447;
select * from AMZ_BONUS_PO WHERE ID_SERVCO = 447;
select * from AMZ_CATEGR_PA WHERE ID_SERVCO = 447;
select * from AMZ_CATEGR_PO WHERE ID_SERVCO = 447;
select * from AMZ_DESCTO_PO WHERE ID_SERVCO = 447;
select * from AMZ_FRNQIA_PO_SERVCO WHERE ID_SERVCO = 447;
select * from AMZ_SERVCO WHERE ID_SERVCO = 447;
select * from AMZ_SERVCO_PA WHERE ID_SERVCO = 447;
select * from AMZ_SERVCO_PO WHERE ID_SERVCO = 447;
select * from BKP_ARQVO_DESCTO WHERE ID_SERVCO = 447;
select * from BKP151207_AMZ_ARQVO_DESCTO WHERE ID_SERVCO = 447;
select * from AMZ_DESCTO_PO_SERVCO WHERE ID_SERVCO = 447;
select * from AMZ_DESCTO_PO_SERVCO_APLCAO WHERE ID_SERVCO = 447;

no rows selected

SQL>
no rows selected

SQL>
no rows selected

SQL>
no rows selected

SQL>
no rows selected

SQL>
no rows selected

SQL>
no rows selected

SQL>
no rows selected

SQL>
no rows selected

SQL>
no rows selected

SQL>
no rows selected

SQL>
no rows selected

SQL>
no rows selected

SQL>
ID_SERVCO NM_SERVCO ID_TP_SERVCO ID_TP_CATEGR
----------------------------------------------------------------- ------------ ------------
   447 eClaro DDD Nacional                                                                                             5            0

SQL>
no rows selected

SQL>
no rows selected

SQL>
no rows selected

SQL>
no rows selected

SQL>
no rows selected

SQL>
no rows selected

SQL>

Bad approach to find out the referenced column. Check the definition of the foreign key instead, it might not use the same column name — Alfabravo, Jun 07 '18 at 17:12
The error message provided tells you right where to look. `ORA-02292: integrity constraint (AMAZONIA.FK_AMZ_FRNQIA_PA_AMZ_SERVCO) violated` you just need to query the ALL_CONSTRAINTS view to find the affected table, and the ALL_CONS_COLUMNS view to know which column(s) are used. In both cases you would limit the queries to `OWNER='AMAZONIA' and CONSTRAINT_NAME='FK_AMZ_FRNQIA_PA_AMZ_SERVCO'` — Sentinel, Jun 07 '18 at 17:37

score 1 · Accepted Answer · answered Jun 07 '18 at 17:21

Yes, you need to look up the violated constraint in the list of all constraints:

SELECT r_owner, r_constraint_name
  FROM all_constraints 
 WHERE owner='AMAZONIA'
   AND constraint_name='FK_AMZ_FRNQIA_PA_AMZ_SERVCO';

This is the name of the primary or unique key of the parent table. To look up it's columns, you can use:

SELECT column_name
  FROM all_cons_columns
 WHERE owner = xxx
   AND constraint_name = yyy;

score 0 · Answer 2 · answered Jun 07 '18 at 17:17

It sounds like you might have another foreign key that you don't know about: try this solution to get a list of all foreign keys. For complicated systems like this, I try to include an [IsActive] boolean column - instead of deleting a record which may have children, you just mark it inactive. HTH!

ORACLE - child record not found

2 Answers2