约束违例异常ORA-00001 [英] Constraint Violation Exception ORA-00001

问题描述

我正在使用Oracle数据库.我们发现我们的服务呼叫频繁失败.当我查看日志时，在表上看到以下异常

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:违反了唯一约束(DBSCHEMA.IDX_CO_DETAILS).

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

我已经检查了表上的Index，以获取索引名称DBSCHEMA.IDX_CO_DETAILS.

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

它不包含任何列(INCLUDE_COLUMN为null).我怎么知道这个约束是做什么用的?是主键约束吗?

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?

我们将休眠用于ORM.下面是休眠上下文中的回溯轨迹

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)

推荐答案

A 唯一约束可以强制实现唯一性.与主键约束不同，它将允许空值.

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.

您可以通过在 all_constraints .链接对列CONSTRAINT_TYPE进行解码，例如P是主键，而U是唯一键.

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'

要找出约束中的哪些列，请使用 all_cons_columns 代替，或将两者合并为一个查询:

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'

对于任何一个查询，您都可以添加附加条件and constraint_name = 'IDX_CO_DETAILS'，以查找可能引起问题的特定约束的详细信息.

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.

基于几个原因，您的评论有些令人惊讶.即使是系统创建的约束，例如在创建表时在没有指定名称的情况下直接定义的约束，也应该显示出来.另外，约束名称IDX...暗示它是一个索引.

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'

我希望此查询返回的OBJECT_TYPE是'INDEX'.

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'

根据您的错误判断，我还希望此查询返回的列UNIQUNESS为'UNIQUE'.

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.

您还可以使用系统软件包 dbms_metadata 跟踪对象的DDL；小心，它会返回血块.

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

参数schema是可选的.

这篇关于约束违例异常ORA-00001的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！