In SQLite3, how do I troubleshoot a "Error: UNIQUE constraint failed" from an "insert into select"?

Question

When I get a Error: UNIQUE constraint failed, is there a way to see which value caused the unique constraint violation? I have evidence that other databases tell you this information (see the DETAIL line in the question), but after an hour+ of research, I can't find if SQLite3 can do this. If it can, how do you enable this feature?

BTW, I'm not looking for workarounds like this answer, I want to learn how to troubleshoot this.

I am using the sqlite3 commandline client.

Answer 1

You could use SQLite's upsert syntax to find these colliding values.

Try something like this, maybe?

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 sure I have the syntax exactly right.) This will tag your duplicate values with -dup123456 like tags. This only works if id1 and id2 are text.

But, if these are your only two columns and together their values are the PK, you can simply ignore the issue by saying ON CONFLICT() DO NOTHING. If your application's integrity won't suffer by ignoring those dups.

This syntax was added to SQLite with version 3.24.0 (2018-06-04).

Edit you can run a separate SELECT to detect the collisions before you attempt the update. Maybe something like this will help. Note my use of the WITH common table expression as a way to make the query clearer.

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

Answer 2

It depends on if there are items already on the table, but I would generally write a quick row_number() with the partition being the key. Then write a select around that where row number is greater then 1. https://www.sqlitetutorial.net/sqlite-window-functions/sqlite-row_number/ would get a good head start.

This will be incorrect because I am not working with an entire set, but:

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;

This gives you row numbers, then the other select only cares about those items that have more then one matching row.

Edited

For the edit / comment below.

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;

Really messy, but will be a dirty way to find the duplicates.