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

Answer 1

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 = <<name of constraint from the error message>>
   AND owner           = <<owner of the table>>
   AND table_name      = <<name of the table>>

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.

Answer 2

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