插入或忽略违反外键约束的行 [英] insert or ignore rows that violate foreign key constraints sqlite

问题描述

我试图在表中插入许多行，其中一些违反了该表上的外键约束.我想跳过添加违反此约束的行，并成功添加所有正确的行.但是，即使在查询中使用 insert或忽略，我也会收到错误消息.

I am trying to insert many rows into my table, some of which violate a foreign key constraint on that table. I want to SKIP adding the rows that violate this constraint and add all the correct rows successfully. However, I get an error even if I use insert or ignore in my query.

在SQLite中有什么方法可以做到这一点?

Is there any way to do this in SQLite?

推荐答案

您可以使用不存在来过滤 insert 语句:

You can use not exists to filter in the insert statement:

insert into table2 ( . . . )
    select . . . 
    from table1 t1
    where exists (select 1
                  from table2 t2
                  where t2.pk = t1.t2pk
                 );

也就是说，甚至都不要尝试插入.

That is, don't even attempt the insert.

这篇关于插入或忽略违反外键约束的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！