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?
To correct this problem, you need to update or delete the value into the child table first and then you can delete the corresponding value into the parent table. For example, if you had created the following foreign key (parent-child relationship).
The Solution In order to remedy this error, you will need to insert the value that you attempted to place in the child table into the parent table first. Once inserted as a parent row, you can go back and insert the value into the child table.
The ORA-02292 error indicates that an “integrity constraint <constraint name> was violated – child record found”. What this indicates is that the user attempted to delete a record from a parent table (which is referenced by a foreign key), but a record in the child table exists.
Entity Integrity constraint : On inserting NULL values to any part of the primary key of a new tuple in the relation can cause violation of the Entity integrity constraint.
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.
If you want to disable the constraint from the name to solve ORA-02292.
Look for the table name bounded to that constraint
SELECT owner, table_name FROM dba_constraints WHERE constraint_name = '{CONSTRAINT_NAME}';
Disable constraint (this command should be executed by an admin user)
ALTER TABLE {TABLE_NAME} DISABLE constraint {CONSTRAINT_NAME} cascade;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With