sql – INSERT […] ON CONFLICT可用于外键违规吗？

=> 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已存在或引用的项不存在,我想要一个跳过插入的条目的insert语句.第一个很容易做到：

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

是否有可能在这里检查是否存在外键？

解决方法

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

UPSERT本身(INSERT …… ON CONFLICT DO NOTHING)仅对唯一违规做出反应. The manual:

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!