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

Hindorff

Is the sequence the only constraint, or are there others? – Appraisal

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.

Stomachic

Alternatively, you can use this query: SELECT * FROM ALL_CONS_COLUMNS A JOIN ALL_CONSTRAINTS C ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.CONSTRAINT_NAME LIKE '%SYS_C0010988%'; . Just replace the CONSTRAINT_NAME: SYS_C0010988 at the end, with the one in your error message. – Hopehopeful

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

Isaacisaacs

Saved the day! Dropped the existing row – Psychiatry

In my case there was unique index applied on one of the column apart from primary key column. when I changed the value for that column the error resolved. So the take away is don't think that your pk column is only unique also check other columns where the unique index is applied. – Pacifist

Answer 3

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.

Valuer

what if it's just a dot without the name in the parentheses? e.g.: ORA-00001: unique constraint (.) violated – Refund

Answer 4

Error message looks like this

Error message => 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 <<owner of the table>> and <<name of the table>> for unique_constraint

select *
from DBA_CONSTRAINTS
where CONSTRAINT_NAME = '<unique_constraint_name>';

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

    -- 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 (<any one column used in constraint > ) > 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.

Cauthen

Answer 5

Check the sequences in the database that might be causing the issue for that particular table. I changed the sequence for the table that was causing the error and it worked.

Huckster

Answer 6

I had the same problem when I tried to insert data with merge, update, insert.

I fixed this issue using trim in compare.

Code that didn't work:

...on (c.CHAR_TYPE_CD = n.CHAR_TYPE_CD and c.CHAR_VAL = n.CHAR_VAL and c.LANGUAGE_CD = n.LANGUAGE_CD)

Successful alternative:

...on (trim (c.CHAR_TYPE_CD) = n.CHAR_TYPE_CD and trim (c.CHAR_VAL) = n.CHAR_VAL and trim (c.LANGUAGE_CD) = n.LANGUAGE_CD)

Benzoyl

Please double check that my edit is correct (and eventually improve it) – Fichu

Answer 7

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

Wolverine