oracle 问题 ORA-00001：INSERT/UPDATE 中违反了唯一约束

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

提问by pri_dev

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

我试图通过应用程序在表中插入一些值并得到问题 ORA-00001：违反唯一约束。我看到序列与表的最高 id 不同步，但即使修复了序列号，错误仍然存​​在。我怎样才能更多地调试这个错误，oracle 日志是否给出了更多错误？我怎样才能看到oracle日志？谢谢普里扬克

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

更新：我们正在使用审计日志插件，在 User 的域类中，我们捕获保存事件并将条目记录到审计日志中

So in User class we do:

所以在 User 类中我们这样做：

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

当我们在 onSave 事件中没有上面的代码时，用户创建成功。
我假设它与我们在一个实例上使用的休眠事务有关，那是死亡或当前事务由于该保存而死亡。
如果我们不使用交易，我们会得到一个异常"org.hibernate.HibernateException: No Hibernate Session bound to thread, and configuration does not allow creation of non-transactional one here"不知道如何解决这个问题..谢谢

回答by Justin Cave

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 INSERTor UPDATEagainst the data already in the table to determine why the constraint is being violated.

一旦您知道哪些列受到影响，您就可以将您尝试使用的数据INSERT或UPDATE与表中已有的数据进行比较，以确定违反约束的原因。

回答by Balaji Boggaram Ramanarayan

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

由于违反唯一约束而发生此 ORA 错误。

ORA-00001: unique constraint (constraint_name) violated

This is caused because of trying to execute an INSERTor UPDATEstatement that has created a duplicate value in a field restricted by a unique index.

这是因为尝试执行在受唯一索引限制的字段中创建了重复值的INSERTorUPDATE语句。

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

• 更改约束以允许重复，或
• 删除唯一约束，或者您可以更改 SQL 以避免重复插入

回答by Codo

Oracle's error message should be somewhat longer. It usually looks like this:

Oracle 的错误信息应该更长一些。它通常看起来像这样：

ORA-00001: unique constraint (TABLE_UK1) violated

The name in parentheses is the constrait name. It tells you which constraint was violated.

括号中的名称是约束名称。它会告诉您违反了哪个约束。

回答by Singh

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."

ORA-00001 在以下情况下发生：“查询尝试在表中插入“重复”行”。它使唯一约束失败，因此查询失败并且行未添加到表中。”

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:

查找unique_constraint中使用的所有列，例如a列、b列、c列、d列共同创建unique_constraint，然后使用以下查询从源数据中查找重复的记录：

-- 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:

然后使用 Justin Cave 的查询（粘贴在下面）来查找 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.

您可以从源数据中删除该重复记录（在我的特定情况下这是一个选择查询，正如我在“插入选择”中所经历的那样）或修改以使其唯一或更改约束。

回答by jemmy ginting

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

选择索引，然后选择需要的索引，然后选择 sql 并单击操作，然后单击重建