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

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

1 2 3 4 5 6

insert into target_table   select *   from   massive_table   where  last_row_is_a_duplicate = 'Y';   ORA-00001: unique constraint (...) violated

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

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

啊！

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

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

• 使用子查询停止添加现有键
• 将ignore_row_on_dupkey_index提示添加到插入
• 使用DML错误日志记录来捕获错误
• 进行约束 deferrable

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

什么是主键或唯一约束？

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

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14

create table accounts (   username varchar2(10)     not null     unique,   given_name varchar2(512)     not null );   insert into accounts ( username, given_name )   values ( 'chrissaxon', 'Chris' ); insert into accounts ( username, given_name )   values ( 'chrissaxon', 'Christopher'  );   ORA-00001: unique constraint (CHRIS.SYS_C0018134) violated

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

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

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

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

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

1 2 3 4 5 6 7 8 9 10 11

create table accounts (   account_id integer     generated as identity     not null     primary key,   username varchar2(10)     not null     unique,   given_name varchar2(512)     not null );

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

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

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

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

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

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

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

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

使用子查询跳过重复的行

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

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

1 2 3 4 5 6 7

insert into accounts ( username, given_name )   select username, given_name   from   accounts_stage acst   where  not exists (     select * from accounts acct     where  acct.username = acst.username   );

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

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

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

使用ignore_row_on_dupkey_index提示忽略重复的行

最简单的方法是向查询添加提示。在11.2中添加的ignore_row_on_dupkey_index提示无提示地忽略了重复的值：

1 2 3

insert /*+ ignore_row_on_dupkey_index ( acct ( username ) ) */   into accounts acct ( username, given_name )   select username, given_name from accounts_stage;

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

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

1 2 3

insert /*+ ignore_row_on_dupkey_index ( acct ( username ) ) */   into accounts acct ( username, given_name )   values ( 'chrissaxon', 'Chris' );

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

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

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

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

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

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

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

1 2 3

insert into accounts ( username, given_name )   select distinct username, given_name   from   accounts_stage;

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

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

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

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13

exec dbms_errlog.create_error_log ( 'accounts' );   insert into accounts ( username, given_name )   values ( 'chrissaxon', 'Chris' )   log errors into err$_accounts reject limit unlimited;   insert into accounts ( username, given_name )   values ( 'chrissaxon', 'Christopher' )   log errors into err$_accounts reject limit unlimited;   insert into accounts ( username, given_name )   select username, given_name from accounts_stage   log errors into err$_accounts reject limit unlimited;

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

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

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

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

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

1 2 3 4 5 6 7

insert into accounts ( username, given_name )   values ( 'chrissaxon', 'Christopher' )   log errors into err$_accounts ('load name')   reject limit unlimited;     select * from err$_accounts where  ora_err_tag$ = 'load name';