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

ora-00001, oracle10g, primary-key, unique-constraint

Solution

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

Problem

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?

Original source