Constraint Violation Exception ORA-00001

Question

I am using Oracle database. We are seeing frequent failures in calls to our service. When I looked at the logs I am seeing following exceptions on a table

java.sql.BatchUpdateException: ORA-00001: unique constraint (DBSCHEMA.IDX_CO_DETAILS) violated.

I have checked the Index on the table for index name DBSCHEMA.IDX_CO_DETAILS .

It did not include any column's( INCLUDE_COLUMN is null) . How can I know what is this constraint for ? Is it primary key constraint?

We are using hibernate for ORM. Below is the back trace in hibernate context

Caused by: org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:94)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
    at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)
    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1027)
    at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:365)

Ben Ben · Accepted Answer · 2012-06-10T15:07:23

A unique constraint enforces, well, uniqueness. It will allow nulls, unlike a primary key constraint.

Your error means that you are inserting duplicate data when the database has been configured to explicitly prohibit that.

You can find out what constraints are on a table by running the following query on all_constraints. The link decodes the column CONSTRAINT_TYPE, for instance P is a primary key and U a unique key.

select *
  from all_constraints uc
 where uc.table_name = 'MY_TABLE'
   and owner = 'DBSCHEMA'

To find out what columns are in a constraint use all_cons_columns instead, or combining the two into one query:

select uc.*, ucc.column_name, ucc.position
  from all_constraints uc
  join all_cons_columns ucc
    on uc.owner = ucc.owner
   and uc.table_name = ucc.table_name
   and uc.constraint_name = ucc.constraint_name
 where uc.table_name = 'MY_TABLE'
   and uc.owner = 'DBSCHEMA'

To either query you can add the additional condition and constraint_name = 'IDX_CO_DETAILS' to find out details of the specific constraint that seems to be causing your problem.

Your comment is a little surprising for a couple of reasons. Even a system created constraint, for instance one that was defined in-line when the table was created without a name being specified should show up. Also, the constraint name IDX... implies that it's an index.

IF you run the following query it should tell you if the object exists in the database:

select *
  from all_objects
 where object_name = 'IDX_CO_DETAILS'

I would expect that the OBJECT_TYPE returned by this query is 'INDEX'.

Following on from that the following query will return every index with that name, the type of index, the table it is associated with and the owner of that table.

select *
  from all_indexes
 where index_name = 'IDX_CO_DETAILS'

Judging by your error I would further expect that the column UNIQUNESS returned by this query is 'UNIQUE'.

This should help you track down the object.

You can also use the system package dbms_metadata to track down the DDL of the object; be careful it returns a clob.

select dbms_metadata.get_ddl('INDEX','IDX_CO_DETAILS', schema => 'DBSCHEMA') 
  from dual

the parameter schema is optional.

Constraint Violation Exception ORA-00001

1 Answers