ORA-00001:唯一约束(MYUSER.ADI_PK)被违反

Question

我有两张桌子，adv_institution和institution。institution有5000+行，而adv_institution有1400+

我想使用Oracle从adv_institution从institution将记录进行回填。这两张表有四个共同的字段，我可以用来回填.

这是我的全部合并语句

merge into
  adv_institution to_t
using (
  select
    uni.*,
    adv_c.country_cd as con_code_text
  from
    (
      select 
        institution_cd, 
        name,
        institution_status,
        country_cd
      from 
        institution uni
      where
        uni.institution_status = 'ACTIVE' and
        uni.country_cd is not null
      group by
        institution_cd,
        name,
        institution_status,
        country_cd
      order by
        name  
    ) uni,
    country_cd c_cd,
    adv_country adv_c
  where
    uni.country_cd = c_cd.country_cd and
    c_cd.description = adv_c.country_cd
) from_t
on
(
  to_t.VENDOR_INSTITUTION_CD = from_t.INSTITUTION_CD or
  to_t.INSTITUTION_CD = from_t.NAME
)
WHEN NOT MATCHED THEN INSERT (
  to_t.INSTITUTION_CD,
  to_t.INSTITUTION_NAME,
  to_t.SHORT_NAME,
  to_t.COUNTRY_CD,

  to_t.NOTE,
  to_t.UNIT_TERMINOLOGY,
  to_t.COURSE_TERMINOLOGY,
  to_t.CLOSED_IND,

  to_t.UPDATE_WHO,
  to_t.UPDATE_ON,
  to_t.CALLISTA_INSTITUTION_CD
)
VALUES (
  from_t.NAME,
  from_t.NAME,
  '',
  from_t.con_code_text,

  '',
  'UNIT',
  'COURSE',
  'N',

  'MYUSER',
  SYSDATE,
  from_t.institution_cd
);

我得到的错误是

错误报告- ORA-00001:唯一约束(MYUSER.ADI_PK)违反了

ADI_PK意味着adv_institution.institution_cd是一个主键，它必须是唯一的。

这是因为在WHEN NOT MATCHED THEN INSERT中有一个insert语句。我将from_t.NAME插入to_t.INSTITUTION_CD中。

当插入到from_t.NAME中时，它看起来至少有两次相同的值。

但我做了一个小组声明，以确保from_t.NAME是唯一的：

(
      select 
        institution_cd, 
        name,
        institution_status,
        country_cd
      from 
        institution uni
      where
        uni.institution_status = 'ACTIVE' and
        uni.country_cd is not null
      group by
        institution_cd,
        name,
        institution_status,
        country_cd
      order by
        name  
    ) uni

我不确定我是否正确地理解了这个问题。我尽我所能，但还是没有运气。

Answer 1

我认为你的主要问题是通过小组讨论。

请考虑以下示例：

 desc temp_inventory;
 Name                  Type
 --------------------- -----------
 WAREHOUSE_NO          NUMBER(2)
 ITEM_NO               NUMBER(10)
 ITEM_QUANTITY         NUMBER(10)

WAREHOUSE_NO    ITEM_NO    ITEM_QUANTITY
1               1000       100
1               2000       200
1               2000       300

如果我写一个查询，我希望warehouse_no是唯一的：

select warehouse_no,item_quantity 
from temp_inventory 
group by warehouse_no,item_quantity

它将返回相同的3行。相反，我想按..。

select warehouse_no,sum(item_quantity)
from temp_inventory 
group by warehouse_no

这将使warehouse_no在这种情况下是独一无二的！

此外，在有VARCHAR2列的情况下，您可以在它们上使用MAX、MIN作为聚合函数和group by，以便在查询中生成唯一的键。

示例：

Select object_type, min(object_name) 
from user_objects group by object_type;

这将使object_type惟一&只返回一个对应的对象名。

所以请注意，如果有重复的，最后一些记录将根据聚合函数被删除。

Answer 2

“但我做了一个小组声明，以确保from_t.NAME是唯一的：”

但是您的查询没有做到这一点。它生成一组不同的(institution_cd,name,institution_status,country_cd)组合。显然，这样的集合可以包含name的多个递归，每个country_cd的不同值都有一个递归。由于您的键中有四个元素，您实际上是在保证您的集合将有多个name出现。

这与ON条件中的or组合在一起，这意味着即使在目标表中to_t.INSTITUTION_CD = from_t.NAME已经有记录，如果to_t.VENDOR_INSTITUTION_CD = from_t.INSTITUTION_CD仍然触发不匹配的逻辑。

问题是合并语句是原子的。来自USING子查询的记录集必须包含唯一的键。当Oracle在结果集中发现第二个相同的name时，它没有说明，我已经合并了其中一个，让我们跳过它。它必须抛出ORA-00001，因为甲骨文无法知道应用了哪条记录，哪种(institution_cd,name,institution_status,country_cd)组合是正确的。

要解决这个问题，您需要更改USING查询，以生成具有唯一键的结果集。这是您的数据模型，您了解它的业务规则，因此您可以正确地重写它。但也许是这样的：

  select 
    name,
    max(institution_cd) as institution_cd,
    institution_status,
    max(country_cd) as country_cd
  from (
    institution uni
  where
    uni.institution_status = 'ACTIVE' and
    uni.country_cd is not null
  group by
    name,
    institution_status
  order by
    name  
) uni

然后，可以将MERGE ON子句简化为：

on
(
  to_t.INSTITUTION_CD = from_t.NAME
)

在子查询中使用MAX()是一个不雅的传说。我希望你能适用更好的商业规则。