issue ORA-00001 unique constraint violated coming in INSERT UPDATE

Question

I am trying to insert some values in table throught the application and get issue ORA-00001  unique constraint violated  I see that sequences are out of sync with the highest id of the table  but even after fixing the sequence number the error still persists   How can I debug this error more  does oracle logs give more error  how can I see the oracle logs  Thanks Priyank  update  we are using the audit logging plugin and in the domain class for User we catch the save event and log the entry into the audit log  So in User class we do    class User      some attributes  constraints  mappings  def onSave      Graaudit aInstance   new Graaudit             aInstance eventType   GRA User Create           aInstance eventDescription    GRA User Created           aInstance objectid   username          aInstance objecttype    GRAUSER           aInstance user id   RequestContextHolder currentRequestAttributes   session username            aInstance withTransaction                aInstance save                          When we dont have the above code in the onSave event the User is created successfully  I am assuming its related to hibernate transaction which we are using on aInstance  thats dying or the current transaction is dying due to that save  If we dont use the transaction we get an exception  org hibernate HibernateException  No Hibernate Session bound to thread  and configuration does not allow creation of non-transactional one here  Not sure how to fix this issue   Thanks

User · Answer

This ORA error is occurred because of violation of unique constraint.

ORA-00001: unique constraint (constraint_name) violated

This is caused because of trying to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.

You can resolve this either by

changing the constraint to allow duplicates, or
drop the unique constraint or you can change your SQL to avoid duplicate inserts

User · Answer

The error message will include the name of the constraint that was violated  there may be more than one unique constraint on a table    You can use that constraint name to identify the column s  that the unique constraint is declared on  SELECT column name  position   FROM all cons columns  WHERE constraint name    lt  lt name of constraint from the error message gt  gt     AND owner              lt  lt owner of the table gt  gt     AND table name         lt  lt name of the table gt  gt    Once you know what column s  are affected  you can compare the data you re trying to INSERT or UPDATE against the data already in the table to determine why the constraint is being violated

User · Answer

Error message looks like this  Error message   gt  ORA-00001  unique constraint  schema unique constraint name  violated   ORA-00001 occurs when   a query tries to insert a  duplicate  row in a table    It makes an unique constraint to fail  consequently query fails and row is NOT added to the table    Solution    Find all columns used in unique constraint  for instance column a  column b  column c  column d collectively creates unique constraint and then find the record from source data which is duplicate  using following queries   -- to find  lt  lt owner of the table gt  gt  and  lt  lt name of the table gt  gt  for unique constraint  select   from DBA CONSTRAINTS where CONSTRAINT NAME     lt unique constraint name gt      Then use Justin Cave s query  pasted below  to find all columns used in unique constraint     SELECT column name  position   FROM all cons columns   WHERE constraint name    lt  lt name of constraint from the error message gt  gt     AND owner              lt  lt owner of the table gt  gt     AND table name         lt  lt name of the table gt  gt       -- to find duplicates      select column a  column b  column c  column d     from table     group by column a  column b  column c  column d     having count   lt any one column used in constraint  gt     gt  1    you can either delete that duplicate record from your source data  which was a select query in my particular case  as I experienced it with  Insert into select   or modify to make it unique or change the constraint

User · Answer

select the  index then select the ones needed then select sql and click action then click rebuild

User · Answer

Oracle s error message should be somewhat longer  It usually looks like this   ORA-00001  unique constraint  TABLE UK1  violated   The name in parentheses is the constrait name  It tells you which constraint was violated

[oracle] issue ORA-00001: unique constraint violated coming in INSERT/UPDATE

Examples related to oracle

Examples related to hibernate

Examples related to transactions