可以将INSERT [...] ON CONFLICT用于违反外键的行为？ [英] Can INSERT [...] ON CONFLICT be used for foreign key violations?

问题描述

给出

=> select * from referenced;
 referenced_id | name  
---------------+-------
             1 | one
             2 | two
             3 | three

和

=> select * from entries;
 entry_id | referenced_id |      name      
----------+---------------+------------------
        1 |             3 | references three

其中 referenced_id 和 entry_id 是主键。

我想为项使用插入语句如果 entry_id 已经存在或所引用的项目不存在，则跳过插入。第一个很容易做到：

I want an insert statement for entries that skips insertion if either the entry_id already exists or the referenced item does not exist. The first is easily done:

INSERT INTO entries
VALUES (1, 2, 'references two')
ON CONFLICT (entry_id) DO NOTHING;

是否也可以在此处检查外键的存在？

Is it possible to check for the existence of the foreign key here too?

推荐答案

是的，将输入行连接到被引用的表，从而删除FK列上不匹配的行：

Yes, join your input rows to the referenced table, thereby removing rows without a match on the FK column:

INSERT INTO entries(entry_id, referenced_id, name)
SELECT val.entry_id, val.referenced_id, val.name
FROM  (
  VALUES (1, 2, 'references two')
         -- more?
  ) val (entry_id, referenced_id, name)
JOIN   referenced USING (referenced_id)  -- drop rows without matching FK
ON     CONFLICT (entry_id) DO NOTHING;   -- drop rows with duplicate id

UPPER本身（ INSERT ...发生冲突时，什么都不做）只会对独特的违规行为做出反应。 手册：

The UPSERT itself (INSERT ... ON CONFLICT DO NOTHING) only reacts to unique violations. The manual:

ON CONFLICT 可用于指定引发唯一约束或排除约束违反错误的替代操作。 （请参见下面的冲突条款。）

ON CONFLICT can be used to specify an alternative action to raising a unique constraint or exclusion constraint violation error. (See ON CONFLICT Clause below.)

由于 VALUES 表达式现在未附加到 INSERT 直接，列类型不是从目标表派生的。使用非基本类型时，您可能需要显式投射输入值。参见：

Since the VALUES expression is now not attached to an INSERT directly, column types are not derived from the target table. You may need to cast input values explicitly when operating with non-basic types. See:

• 在更新多行时使用NULL类型

• Casting NULL type when updating multiple rows

这篇关于可以将INSERT [...] ON CONFLICT用于违反外键的行为？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！