Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 602
Thorsten Avatar asked Feb 01 '10 10:02

Thorsten


Video Answer


2 Answers

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

like image 147
VolkerK Avatar answered Oct 23 '22 14:10

VolkerK


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

like image 20
bert Avatar answered Oct 23 '22 13:10

bert