如何排查SQLite3中INSERT INTO SELECT的UNIQUE约束失败及违规值？

SQLite命令行默认的UNIQUE constraint failed错误提示不会直接返回冲突的具体值，但可以通过以下几种方式排查：

• 定位唯一约束对应的列
  先明确目标表中哪一列（或列组合）是唯一约束的主体，执行以下命令查看表结构或唯一索引：

  -- 查看表的创建语句，包含约束定义
  SELECT sql FROM sqlite_master WHERE type='table' AND name='你的目标表名';
  
  -- 查看表的唯一索引（唯一约束通常对应唯一索引）
  SELECT name, sql FROM sqlite_master WHERE type='index' AND tbl_name='你的目标表名' AND sql LIKE '%UNIQUE%';
  

• 预查询冲突数据
  将INSERT逻辑拆解为SELECT语句，直接找出源数据中与目标表唯一列重复的记录。比如目标表唯一列为user_id，原插入语句为INSERT INTO target SELECT * FROM source，则执行：

  -- 单唯一列的情况
  SELECT s.* FROM source s
  INNER JOIN target t ON s.user_id = t.user_id;
  
  -- 多列组合唯一约束的情况
  SELECT s.* FROM source s
  INNER JOIN target t ON s.col1 = t.col1 AND s.col2 = t.col2;
  

  这个查询会直接返回所有触发冲突的记录，你可以从中看到具体的违规值。

• 开启详细错误提示（SQLite 3.37.0+适用）
  从SQLite 3.37.0版本开始，新增了控制错误提示详细程度的PRAGMA命令。执行以下步骤：

  1. 先在命令行中开启详细错误输出：

     PRAGMA error_message_detail=full;
     

  2. 重新运行你的INSERT INTO SELECT语句，此时错误信息会包含冲突的列名和具体值，格式类似：Error: UNIQUE constraint failed: target.user_id (value: 123)。
     可以用sqlite3 --version命令检查当前SQLite版本是否符合要求。

内容的提问来源于stack exchange，提问作者Daniel Kaplan