oracle - integrity constraint violated - child record found

7
votes

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?

2
databaseoracleplsqlconstraints
Do you have circular dependencies? If the answer is no then just insert in the correct order. If you do why aren't the constraints deferred all the time? Doing this at the beginning of a procedure would indicate a problem with your schema / data-model. - Ben
Yes, I know that there are big problems with this data-model (written by other people of course), but it was ok before, and it started fail now. So I need to find out what causes it. Was it because of deletions, or insertions in particular. - javagirl

2 Answers

13
votes

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.

3
votes

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;