oracle - integrity constraint violated - child record found

Question

I have a huge pl/sql stored procedure, where we make some deletions as long as insertions. Procedure starts with the statement

 EXECUTE IMMEDIATE 'SET CONSTRAINTS ALL DEFERRED'

And at the last commit statement I receive ORA-02292: integrity constraint AAA violated. The questions is that I don't know which statement exactly causes it, because I have both deletion from parent table (before child one) and insertions into child table before parent. I tried to google it, but everywhere it's said that 02292 happens when I try to delete only.

Could this error happen when I try to insert value in the child table but there is no this entry in the parent?

Also, what is the difference between 02292 and 02291?

Solution 1

ORA-02292 indicates that the error occurred because A) the constraint has no ON DELETE clause specified, and B) you deleted a row from the master table which had matching references in the child table. Your choices are to modify the constraint so have an ON DELETE CASCADE or to ensure that all child records are deleted before deleting from the master. My preference would be to add ON DELETE CASCADE but I suppose there could be reasons not to do so. See ORA-02292.

ORA-02291 is sort of the opposite of this. ORA-02291 will be raised if you attempt to insert a row into a child table, but the key field values on your new child row as specified in the constraint do not exist in the master table. See ORA-02291.

Solution 2

If you want to disable the constraint from the name to solve ORA-02292.

1. Look for the table name bounded to that constraint

   SELECT owner, table_name FROM dba_constraints WHERE constraint_name = '{CONSTRAINT_NAME}';

2. Disable constraint (this command should be executed by an admin user)

   ALTER TABLE {TABLE_NAME} DISABLE constraint {CONSTRAINT_NAME} cascade;