在SQLite UPDATE中模拟ORDER BY以处理唯一性约束 [英] Simulate ORDER BY in SQLite UPDATE to handle uniqueness constraint

问题描述

因此我在SQLite 3中有一个表:

I have a table in SQLite 3 thus:

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

以下是一些示例数据，以供说明:

Here is some sample data, for illustration:

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

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

I wish to add 1 to the priority of all cells with priority > 1. Here is my first attempt:

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

此操作失败，大概是因为未按顺序进行更新，从而使UPDATE可以尝试将priority列中的一个单元格设置为现有单元格的值.所以，这是我的第二次尝试:

This fails, presumably because the update is not ordered, allowing the UPDATE to attempt to set one of the cells in the priority column to the value of an existing cell. So, here is my second attempt:

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进行编译选项.

This, too, fails, presumably because my installation of SQLite 3 was not compiled with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT option.

因为我最终可能希望将我的SQL语句与Android上的SQLite一起使用，而后者却没有 >启用了SQLITE_ENABLE_UPDATE_DELETE_LIMIT，我最好找到另一种实现目标的方法，而不是重新启用SQLITE_ENABLE_UPDATE_DELETE_LIMIT的SQLite 3.那么，这是我的第三次尝试:

Because I might ultimately want to use my SQL statement(s) with SQLite on Android, and the latter also does not have SQLITE_ENABLE_UPDATE_DELETE_LIMIT enabled, I had better find another way to achieve my aim than re-compiling SQLite 3 with SQLITE_ENABLE_UPDATE_DELETE_LIMIT enabled. Here, then, is my third attempt:

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

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

This, too, fails, presumably because SQLite checks the uniqueness constraint before committing the transaction.

三次尝试失败，但是我敢肯定这是可能的.问题是:如何做；然后，如何做到最好?

Three failed attempts, but I'm sure it's possible. The questions are: how to do it; and within that, how best to do it?

N.B.我宁愿不要依赖任何未经检验的假设.

推荐答案

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

You are right that the problem appears because SQLite checks the constraints after every row update and not at the end of statement or the end of transaction.

我看到此问题的解决方法(SQLite未正确实现UPDATE).假定priority列没有任何负值，我们可以将它们(负值)用作临时值，以避免UNIQUE约束错误:

I see this workaround to the problem (of SQLite not having implemented UPDATE correctly). Assuming that the priority column does not have any negative values, we can use them (negative values) as temporary to avoid the UNIQUE constraint errors:

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

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

这篇关于在SQLite UPDATE中模拟ORDER BY以处理唯一性约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！