SQLite3重排position列触发UNIQUE约束报错 求最优解决方案

你遇到的UNIQUE约束冲突本质是条目前移时的更新顺序问题：
当end_position < start_position（前移）时，你需要把[end_position, start_position - 1]区间内的所有条目位置+1。SQLite默认按position升序执行更新操作，比如你要把20到29的位置统一+1，会先把位置20改成21，此时原位置21的条目还没更新，就会出现两个position=21的记录，直接触发UNIQUE约束报错。
而条目后移时你是把区间内的位置-1，且第一步已经删掉了start_position的记录，空出了最小编号的位置，从小到大减1不会出现重复，所以不会报错。

完全不需要删除position的UNIQUE约束，这个约束可以保证业务上位置唯一的合理性，删了反而会引入脏数据风险。只需做三处修改即可：

• 给前移场景的UPDATE语句添加ORDER BY position DESC，从大到小更新位置，避免重复
• 把所有操作合并到同一个事务中，不要分步commit，避免中间步骤出错导致数据丢失
• 增加异常捕获，出错时自动回滚事务，保证数据一致性

def move_player_list_item(start_position, end_position, player_list_item):
    conn = create_connection()
    cur = conn.cursor()
    try:
        # 第一步：删除原位置条目
        query = "DELETE FROM `player_list` WHERE `position`=?;"
        cur.execute(query, (str(start_position),))
        
        if start_position < end_position:
            # 后移：从小到大更新减1
            query = "UPDATE `player_list` SET `position`=`position`-1 WHERE `position`>? AND `position`<=? ORDER BY `position` ASC;"
            cur.execute(query, (str(start_position), str(end_position)))
        elif end_position < start_position:
            # 前移：从大到小更新加1，避免UNIQUE冲突
            query = "UPDATE `player_list` SET `position`=`position`+1 WHERE `position`>=? AND `position`<? ORDER BY `position` DESC;"
            cur.execute(query, (str(end_position), str(start_position)))
        
        # 插入新位置条目
        query = "INSERT INTO `player_list` (`play`, `relative_type`, `relative_number`, `repeats`, `duration_milliseconds`, `duration_human`,`position`) VALUES (?,?,?,?,?,?,?)"
        cur.execute(query, (
            str(player_list_item["play"]),
            str(player_list_item["relative_type"]),
            str(player_list_item["relative_number"]),
            str(int(player_list_item["repeats"])),
            str(int(player_list_item["duration_milliseconds"])),
            str(player_list_item["duration_human"]),
            str(end_position)
        ))
        # 所有操作完成后统一提交
        conn.commit()
        return 1
    except Exception as e:
        # 出错回滚
        conn.rollback()
        raise e
    finally:
        # 关闭连接避免泄漏
        conn.close()

• 你当前把所有参数都转成字符串传入SQL是不必要的，如果position字段是整数类型，直接传整数即可，不需要转str，避免隐式类型转换带来的隐藏问题
• 可以在业务层先校验start_position、end_position的合法性，比如不能小于1、不能超过当前最大条目数，减少不必要的数据库操作

内容的提问来源于stack exchange，提问作者Chris P