在SQLite3中,如何排除“错误:UNIQUE约束从“插入到select”失败?

fumotvh3  于 2023-03-08  发布在  SQLite
关注(0)|答案(2)|浏览(196)

摘要:当我得到一个Error: UNIQUE constraint failed时,有没有办法看到哪个导致了唯一约束冲突?
首先,我想说我已经解决了这个插入语句的问题,我不是问如何修复SQL,我是问如何排除这种错误,这样我就不会在以后花太多时间在它上面。
也就是说,下面是一个导致唯一约束错误的 * 类似 * 查询:

insert into a_table (id1, id2)
select d_table.id1,
    '1' as id2
from b_table
    join c_table on b_table.id = c_table.path_id
    join d_table on c_table.id = d_table.id1
    left join a_table on d_table.id1 = a_table.id1
where a_table.id1 is null
    and b_table.path like 'X:\%'
order by 1;

运行这个程序给了我一个Error: UNIQUE constraint failed: a_table.id1, a_table.id2,我花了一个多小时来排除故障:我没有考虑到在select中可能返回非唯一行,一旦我注意到这一点,修复就像将select更改为select distinct一样简单。
我不确定这是否相关,但这违反了a_table的主键,该主键仅由这两列组成。
此外,a_table已经填充了多页行。
现在回答我的问题:是否有可能看到比所给信息更多的信息?具体来说,如果能看到违反约束的a_table.id1a_table.id2,将非常有帮助,但我在任何地方都找不到有关如何显示该信息的任何信息。
我想重申一下I'm not looking for workarounds like this answer,我想了解如何解决这个问题。
我使用的是sqlite3命令行客户端。

3pmvbmvn

3pmvbmvn1#

这取决于表中是否已经有项,但我通常会快速写一个row_number(),将分区作为键,然后在行号大于1的地方写一个select。https://www.sqlitetutorial.net/sqlite-window-functions/sqlite-row_number/将获得一个良好的开端。
这将是不正确的,因为我没有处理整个集合,而是:

select * from
(
select d_table.id1,
    '1' as id2,
    row_number() over ( partition by d_table.id1, '1' order by d_table.id1) as rowNum
from b_table
    join c_table on b_table.id = c_table.path_id
    join d_table on c_table.id = d_table.id1
    left join a_table on d_table.id1 = a_table.id1
where a_table.id1 is null
    and b_table.path like 'X:\%'
) d where d.rowNum > 1
order by 1;

这将为您提供行号,然后另一个select只关心那些具有多个匹配行的项。

    • 已编辑**

对于编辑/评论如下。

select *
from
(
    select *, 
    row_number() over ( partition by d.id1, d.id2 order by  d_table.id1) as rowNum from
    (
      select * from a_tableid
      union all 
      select d_table.id1,
         '1' as id2
      from b_table
        join c_table on b_table.id = c_table.path_id
        join d_table on c_table.id = d_table.id1
        left join a_table on d_table.id1 = a_table.id1
      where a_table.id1 is null
        and b_table.path like 'X:\%'
    ) d 
) e where e.rowNum > 1
order by 1;

真的很乱,但将是一个肮脏的方式找到重复。

yduiuuwa

yduiuuwa2#

您可以使用SQLite的upsert syntax来查找这些冲突值。
试试这样的吧

insert into a_table (id1, id2)
select  ... whatever ...
  ON CONFLICT() DO UPDATE 
         SET id1=excluded.id1 || '-dup' || RANDOM,
         SET id2=excluded.id2 || '-dup' || RANDOM;

(Not当然我的语法完全正确。)这将用类似-dup123456的标签来标记重复的值。这仅在id1id2是文本时有效。
但是,如果这是您仅有的两列,并且它们的值合在一起就是PK,那么您可以简单地通过ON CONFLICT() DO NOTHING来忽略这个问题,如果忽略这些错误不会影响应用程序的完整性的话。
此语法已添加到版本3.24.0的SQLite(2018年6月4日)。

编辑您可以在尝试更新之前运行一个单独的SELECT来检测冲突。也许类似这样的操作会有所帮助。请注意我使用WITH common table expression是为了使查询更清晰。

WITH newkeys AS (
 select d_table.id1,
        '1' as id2
   from b_table
   join c_table on b_table.id = c_table.path_id
   join d_table on c_table.id = d_table.id1
   left join a_table on d_table.id1 = a_table.id1
  where a_table.id1 is null
    and b_table.path like 'X:\%'
)
SELECT COUNT(*) number_of_duplicates_in_a_table,
       a_table.id1, a_table.id2
  FROM a_table
  JOIN newkeys  
            ON a_table.id1 = newkeys.id1
           AND b_table.id2 = newkeys.id2
 GROUP BY a_table.id1, a_table.id2

相关问题