blocks|key|5284710|text|错误消息将包括被违反的约束的名称(表上可能有多个唯一约束)。可以使用该约束名称来标识在其上声明唯一约束的列|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5284711|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>>|code-block|syntax|javascript|5284712|一旦知道哪些列受到影响，就可以将尝试INSERT或UPDATE的数据与表中已有的数据进行比较，以确定违反约束的原因。|offset|length|style|CODE|5284713|entityMap^0|0|0|I|6|P|6|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

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

<pre><code>SELECT column_name, position
 FROM all_cons_columns
 WHERE constraint_name = &lt;&lt;name of constraint from the error message&gt;&gt;
 AND owner = &lt;&lt;owner of the table&gt;&gt;
 AND table_name = &lt;&lt;name of the table&gt;&gt;
</code></pre>

Once you know what column(s) are affected, you can compare the data you're trying to <code>INSERT</code> or <code>UPDATE</code> against the data already in the table to determine why the constraint is being violated.

blocks|key|1496794|text|Oracle的错误消息应该更长一些。它通常看起来像这样：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1496795|ORA-00001:+unique+constraint+(TABLE_UK1)+violated|code-block|syntax|javascript|1496796|括号中的名称是约束名称。它会告诉您违反了哪个约束。|1496797|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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

<pre><code>ORA-00001: unique constraint (TABLE_UK1) violated
</code></pre>

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

blocks|key|1501470|text|发生此ORA错误是因为违反了唯一约束。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1501471|ORA-00001:+unique+constraint+(constraint_name)+violated|code-block|syntax|javascript|1501472|这是因为尝试执行的INSERT或UPDATE语句在由唯一索引限制的字段中创建了重复值。|offset|length|style|CODE|1501473|您可以通过以下方式解决此问题：|1501474|1501475|更改约束以允许重复，或者|unordered-list-item|1501476|删除唯一约束，或者您可以更改SQL以避免重复插入|1501477|。|1501478|entityMap^0|0|0|9|6|G|6|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|10|8|@$I|11|J|12|K|L]|$I|13|J|14|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|15|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|16|8|@]|9|@]|A|$]]|$1|P|3|Q|5|R|7|17|8|@]|9|@]|A|$]]|$1|S|3|T|5|R|7|18|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|19|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|X|$]]

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

<pre><code>ORA-00001: unique constraint (constraint_name) violated
</code></pre>

This is caused because of trying to execute an <code>INSERT</code> or <code>UPDATE</code> statement that has created a duplicate value in a field restricted by a unique index.

You can resolve this either by 

<ul>
<li>changing the constraint to allow duplicates, or</li>
<li>drop the unique constraint or you can change your SQL to avoid duplicate inserts</li>
</ul>

blocks|key|3867390|text|错误消息如下所示|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3867391|Error+message+=>+ORA-00001:+unique+constraint+(schema.unique_constraint_name)+violated|code-block|syntax|javascript|3867392|ORA-00001在以下情况下发生：“查询试图在表中插入”重复“行”。它使唯一约束失败，因此查询失败，行不会添加到表中。“|3867393|解决方案：|offset|length|style|BOLD|3867394|查找unique_constraint中使用的所有列，例如，列a、列b、列c、列d共同创建unique_constraint，然后使用以下查询从源数据中查找重复的记录：|3867395|--+to+find+<<owner+of+the+table>>+and+<<name+of+the+table>>+for+unique_constraint

select+*
from+DBA_CONSTRAINTS
where+CONSTRAINT_NAME+=+'<unique_constraint_name>';|3867396|然后使用Justin+Cave的查询(粘贴在下面)查找unique_constraint中使用的所有列：|3867397|++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;|3867398|您可以从源数据中删除重复记录(在我的特殊情况下，这是一个select查询，正如我在"Insert+into+select“中所经历的那样)，也可以修改以使其唯一或更改约束。|3867399|entityMap^0|0|0|0|0|5|0|0|0|0|0|0|2E|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|12|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|13|8|@$K|14|L|15|M|N]]|9|@]|A|$]]|$1|O|3|P|5|6|7|16|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|17|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|18|8|@]|9|@]|A|$]]|$1|U|3|V|5|D|7|19|8|@]|9|@]|A|$E|F]]|$1|W|3|X|5|6|7|1A|8|@$K|1B|L|1C|M|N]]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]]|Z|$]]

Error message looks like this

<pre><code>Error message =&gt; ORA-00001: unique constraint (schema.unique_constraint_name) violated
</code></pre>

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:

<pre><code>-- to find &lt;&lt;owner of the table&gt;&gt; and &lt;&lt;name of the table&gt;&gt; for unique_constraint

select *
from DBA_CONSTRAINTS
where CONSTRAINT_NAME = '&lt;unique_constraint_name&gt;';
</code></pre>

Then use Justin Cave's query (pasted below) to find all columns used in unique_constraint:

<pre><code> SELECT column_name, position
 FROM all_cons_columns
 WHERE constraint_name = &lt;&lt;name of constraint from the error message&gt;&gt;
 AND owner = &lt;&lt;owner of the table&gt;&gt;
 AND table_name = &lt;&lt;name of the table&gt;&gt;

 -- 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 (&lt;any one column used in constraint &gt; ) &gt; 1;
</code></pre>

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.

blocks|key|1757641|text|选择索引，然后选择所需的索引，然后选择sql并单击操作，然后单击重建|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1757642|​|1757643|📷|atomic|offset|length|1757644|1757645|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/fef85250ccd42c1aec7c30d3f5de35ea.png|imageAlt^0|0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@$G|V|H|W|1|X]]|A|$]]|$1|I|3|C|5|6|7|Y|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|Q|R|-4]]]]

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

<a href="https://i.stack.imgur.com/Juzvs.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Juzvs.png" alt="enter image description here"></a>

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: 

<pre><code>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()
 }
 }

}
</code></pre>

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 <code>"org.hibernate.HibernateException: No Hibernate Session bound to thread, and configuration does not allow creation of non-transactional one here"</code>
Not sure how to fix this issue..
Thanks

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

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我试图通过应用程序在表中插入一些值，并得到ORA-00001:违反唯一约束的问题。我发现序列与表的最高id不同步，但即使在修复序列号之后，错误仍然存在。如何更多地调试此错误，oracle日志是否会给出更多错误？如何查看oracle日志？谢谢Priyank更新:我们使用审计日志插件，并在User的域类中捕获save事件并...

问问题ORA-00001:插入/更新时违反了唯一约束
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问问题ORA-00001:插入/更新时违反了唯一约束EN