如何跳过Oracle数据库中的重复键错误（ORA-00001）

亦凉 2023-10-05 14:07 12阅读 0赞

这是无处不在的数据加载的祸害。完成了99％的过程，只是因为它在最后几行失败：

这通常会中止整个过程。回滚一切。

这意味着您需要清除数据。然后再次运行整个负载。

啊！

如果负载需要数小时才能完成，则这尤其成问题。

幸运的是，Oracle数据库有几种方法可用于跳过重复的行，以阻止这种情况的发生。在这篇文章中，您将看到如何通过以下方法做到这一点：

*  使用子查询停止添加现有键
 *  将ignore\_row\_on\_dupkey\_index提示添加到插入
 *  使用DML错误日志记录来捕获错误
 *  [进行约束 deferrable][deferrable]

但是首先，快速回顾一下什么是主键和唯一约束以及为什么要使用它们。

什么是主键或唯一约束？

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70][]

能够在表中定位特定行是SQL的一项基本功能。实际上，这是[第一个范式][Link 1]的要求。

在您的应用程序中获取帐户的用户名。为了确保有人输入了正确的密码并在登录时提取其详细信息，您需要找到该用户名的行。并且确保每个用户名最多在帐户表中出现一次。

声明用户unique名将强制第二个属性。如果有人尝试插入重复的名称，则会引发错误：

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 </td> 
 <td> create table accounts ( &nbsp;&nbsp;username varchar2(10) &nbsp;&nbsp;&nbsp;&nbsp;not null &nbsp;&nbsp;&nbsp;&nbsp;unique, &nbsp;&nbsp;given_name varchar2(512) &nbsp;&nbsp;&nbsp;&nbsp;not null ); &nbsp; insert into accounts ( username, given_name ) &nbsp;&nbsp;values ( 'chrissaxon', 'Chris' ); insert into accounts ( username, given_name ) &nbsp;&nbsp;values ( 'chrissaxon', 'Christopher'&nbsp; ); &nbsp; ORA-00001: unique constraint (CHRIS.SYS_C0018134) violated </td> 
 </tr> 
 </tbody> 
</table>

这样可以保证每个用户名最多显示一次。

主键是唯一约束的特例。它具有以下额外属性：

*  其所有列均为必填（not null）
 *  每个表最多可以有一个主键。

那么，如何在使用主键还是唯一约束之间进行选择？

一些表可以具有许多唯一的标识符。常见的原因是代理键–序列分配的值或GUID值在应用程序之外没有任何意义。例如，您可以account\_id在accounts表中添加一列：

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 </td> 
 <td> create table accounts ( &nbsp;&nbsp;account_id integer &nbsp;&nbsp;&nbsp;&nbsp;generated as identity &nbsp;&nbsp;&nbsp;&nbsp;not null &nbsp;&nbsp;&nbsp;&nbsp;primary key, &nbsp;&nbsp;username varchar2(10) &nbsp;&nbsp;&nbsp;&nbsp;not null &nbsp;&nbsp;&nbsp;&nbsp;unique, &nbsp;&nbsp;given_name varchar2(512) &nbsp;&nbsp;&nbsp;&nbsp;not null ); </td> 
 </tr> 
 </tbody> 
</table>

该表只能有一个主键。因此，account\_id且username必须至少是唯一约束。但是哪个应该是*主*键？

该accounts表可能有许多子表引用其中的特定行。例如订单，发票和付款。为确保这些子行指向有效的帐户，请创建一个从子表到父表的外键。

外键指向的列应该是不可变的。不变。这避免了必须从父级到子级级键值更新的问题。例如，如果有人想更改其用户名。

主键和唯一约束都可以成为外键的目标。但是因为您只能有一个主键，所以按照惯例，外键指向此约束。

因此，通过创建account\_id主键，您可以告诉其他开发人员：

*“**这是不可变的值。将其用作外键的目标。**”*

创建唯一约束可以提高数据质量。通常，当有人尝试插入现有值（例如使用现有用户名创建新帐户）时，您想停止这种情况。

但是有时您可能想跳过此错误。在加载已存在的密钥时说。

使用子查询跳过重复的行

如果源数据可能包含表中已存在的键值，则最好避免完全加载这些键值。

使用not exists子查询执行此操作：

这样只会加载accounts\_stage缺少的用户名accounts。

这仅适用于insert ... select。没有与的查询insert ... values。因此，此方法不适用于这些方法。

如果*源*包含重复项，该怎么办？您如何跳过那里的多余行？

使用ignore\_row\_on\_dupkey\_index提示忽略重复的行

最简单的方法是向查询添加提示。在11.2中添加的ignore\_row\_on\_dupkey\_index提示无提示地忽略了重复的值：

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 </td> 
 <td> insert /*+ ignore_row_on_dupkey_index ( acct ( username ) ) */ &nbsp;&nbsp;into accounts acct ( username, given_name ) &nbsp;&nbsp;select username, given_name from accounts_stage; </td> 
 </tr> 
 </tbody> 
</table>

因此，如果accounts\_stage用户名重复，则会添加其中一行，而绕过另一行。

当使用插入值的版本时，这也适用：

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 </td> 
 <td> insert /*+ ignore_row_on_dupkey_index ( acct ( username ) ) */ &nbsp;&nbsp;into accounts acct ( username, given_name ) &nbsp;&nbsp;values ( 'chrissaxon', 'Chris' ); </td> 
 </tr> 
 </tbody> 
</table>

要使用提示，请在表名称或别名后加上以下任一名称：

*  唯一索引的名称
 *  以逗号分隔的唯一索引中的列列表

注意，您只能忽略表中的**一个**约束。如果您要跳过多余的用户名并具有自动生成的主键，这不是问题。但是，如果表上有许多唯一键，则存在限制。

虽然添加提示很容易，但它还有其他一些主要缺点：

*  您不知道哪一行会被忽略
 *  没有什么可以告诉你，有重复的值

因此，如果您想知道是否存在重复项，则需要对数据进行后处理以进行检查。

如果源包含相同的行（每一列具有相同的值），则这可能不会打扰您。当然，如果是这种情况，最好添加distinct子查询：

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 </td> 
 <td> insert into accounts ( username, given_name ) &nbsp;&nbsp;select distinct username, given_name &nbsp;&nbsp;from&nbsp;&nbsp; accounts_stage; </td> 
 </tr> 
 </tbody> 
</table>

但是，如果只有用户名发生冲突，那么您可能想知道。并找出您跳过了哪些行。为此，您需要保留哪些行失败。

使用DML错误记录存储重复值

DML错误记录启用insert，update和delete语句将异常存储在另一个表中。允许语句完成而没有错误。康纳在这段视频中对此进行了讨论：

要使用它，首先必须创建错误日志表。默认情况下，这将创建一个名为的表err$\_<tablename>。

然后在您的SQL中使用log errors子句：

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 </td> 
 <td> exec dbms_errlog.create_error_log ( 'accounts' ); &nbsp; insert into accounts ( username, given_name ) &nbsp;&nbsp;values ( 'chrissaxon', 'Chris' ) &nbsp;&nbsp;log errors into err$_accounts reject limit unlimited; &nbsp; insert into accounts ( username, given_name ) &nbsp;&nbsp;values ( 'chrissaxon', 'Christopher' ) &nbsp;&nbsp;log errors into err$_accounts reject limit unlimited; &nbsp; insert into accounts ( username, given_name ) &nbsp;&nbsp;select username, given_name from accounts_stage &nbsp;&nbsp;log errors into err$_accounts reject limit unlimited; </td> 
 </tr> 
 </tbody> 
</table>

该reject limit默认为零。这意味着如果有任何重复项，您仍然会得到例外。将此设置为unlimited可以忽略所有ORA-00001错误，但是插入仍然成功！

当然，如果负载中有很多错误，则可能是更大问题的征兆。并且您想停止处理。因此，如果要中止负载（如果有多于100条失败行），请设置reject limit 100。

加载完成后，您将需要查询错误记录表以查看是否存在任何问题。

这可能是一个问题，同时有很多负载在运行。您如何知道错误属于哪个进程？

要解决此问题，请在负载中添加标签。然后在以下情况下使用它来找出违规数据：

[deferrable]: https://blogs.oracle.com/sql/how-to-skip-duplicate-key-errors-ora-00001-in-oracle-database#deferrable-constraint
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20201121192217937.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI=,size_16,color_FFFFFF,t_70
[Link 1]: https://en.wikipedia.org/wiki/First_normal_form