Getting ORA-00001(unique constraint violated) when COMMITing?

Question

We're getting a ORA-00001 (unique constraint violated) in a batch job. However, the error occurs when a COMMIT is issued, not at the time the offending record is inserted.

Questions:

• How come that the unique constraint is checked at COMMIT? (Are there some settings we can use so that the check occurs at the time of the INSERT?)
• How can we find out the offending SQL/record that lead to the unique constraint violation?

Any help is appreciated!

---

Additional Information/Question:

The "offending" constraint is marked as IMMEDIATE and NON-DEFERRABLE. Can this be overridden in the transaction?

Answer 1

Constrains can be defined as deferred, meaning that they are checked at commit, not at the time of the data change. See the following 2 links:

http://www.oracle-base.com/articles/8i/ConstraintCheckingUpdates.php

http://www.oracle.com/technology/oramag/oracle/03-nov/o63asktom.html

hope it helps

Answer 2

Constraints can be marked/defined as deferrable. In that case constraint checks can be either "immediate" or "deferred". When defining the constraint you can set a default/initial value, initially immediate or initially deferred. When set to deferred the constraint is enforced not until you commit the transaction.
You can change the behaviour of deferrable constraints e.g. via

set constraints all immediate;

see also: http://www.oracle.com/technology/oramag/oracle/03-nov/o63asktom.html