sql - 在 SQLite UPDATE 中模拟 ORDER BY 来处理唯一性约束

Question

我在 SQLite 3 中有一个表:

sqlite> .schema
CREATE TABLE table1 (id INTEGER PRIMARY KEY NOT NULL,
                     title TEXT UNIQUE NOT NULL,
                     priority INTEGER UNIQUE NOT NULL);

这里是一些示例数据，用于说明:

sqlite> SELECT * FROM table1;
id          title       priority  
----------  ----------  ----------
1           a           1         
2           b           2         
3           c           3         
4           d           4

我希望将所有 priority > 1 的单元格的 priority 加 1。这是我的第一次尝试:

sqlite> UPDATE table1 SET priority = priority + 1 WHERE priority > 1;
Error: column priority is not unique

这失败了，大概是因为更新没有排序，允许 UPDATE 尝试将 priority 列中的单元格之一设置为现有单元格的值。所以，这是我的第二次尝试:

sqlite> UPDATE table1 SET priority = priority + 1 WHERE priority > 1
        ORDER BY priority DESC;
Error: near "ORDER": syntax error

这也失败了，大概是因为我安装的 SQLite 3 不是用 SQLITE_ENABLE_UPDATE_DELETE_LIMIT 编译的选项。

因为我可能最终想在 Android 上将我的 SQL 语句与 SQLite 一起使用，而后者也是 does not启用了 SQLITE_ENABLE_UPDATE_DELETE_LIMIT，我最好找到另一种方法来实现我的目标，而不是在启用 SQLITE_ENABLE_UPDATE_DELETE_LIMIT 的情况下重新编译 SQLite 3。那么，这是我的第三次尝试:

sqlite> BEGIN TRANSACTION;
        UPDATE table1 SET priority = priority + 1 WHERE priority > 1;
        END TRANSACTION;
Error: column priority is not unique

这也失败了，大概是因为 SQLite 在提交事务之前检查了唯一性约束。

三次失败的尝试，但我确信这是可能的。问题是:如何做；在其中，如何最好地做到这一点？

注意我不想依赖任何未经检验的假设。

Answer 1

问题的出现是对的，因为 SQLite 在每次更新行之后而不是在语句结束或事务结束时检查约束。

我看到这个问题的解决方法(SQLite 没有正确实现 UPDATE)。假设 priority 列没有任何负值，我们可以使用它们(负值)作为临时值来避免 UNIQUE 约束错误:

UPDATE table1 SET priority = - (priority + 1) WHERE priority > 1 ;

UPDATE table1 SET priority = - priority WHERE priority < 0 ;