Oracle - integrity violation violated - child record found

Question

I have a huge pl / sql stored procedure where we do some deletion until insertion. The procedure begins with approval

EXECUTE IMMEDIATE 'SET CONSTRAINTS ALL DEFERRED' 

And in the last commit statement, I get ORA-02292: AAA integrity violation violated. The questions are that I don’t know which operator exactly calls it, because I have deletion from the parent table (to the child table) and pasting into the child table in front of the parent. I tried to do this, but everywhere he said that 02292 happens when I try to delete only.

Can this error happen when I try to insert a value in a child table, but there is no this record in the parent?

Also, what is the difference between 02292 and 02291?

Answer 1

ORA-02292 indicates that an error occurred because A) the constraint does not specify an ON DELETE clause, and B) you deleted a row from the main table that had the corresponding links in the child table. Your choice is to change the restriction so that you have DELETE CASCADE or so that all child records are deleted before being deleted from the wizard. My preference would be to add ON DELETE CASCADE, but I suppose there may be reasons not to. See ORA-02292 .

ORA-02291 is the opposite. ORA-02291 will be raised if you try to insert a row in the child table, but the key field values ​​in your new child row, as specified in the restriction, do not exist in the main table. See ORA-02291 .

Answer 2

If you want to disable the restriction on behalf of the solution ORA-02292.

• Find the table name limited to this restriction

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

• Disable restriction (this command must be executed by the administrator user)

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