sql - SQL:当列是唯一且顺序的时，将1(或N)添加到每行的给定列中吗？

Question

我有一个带表的sqlite数据库，我们称之为books。该表具有名为ordering的列，该列是UNIQUE整数列，用作ORDER BY子句的默认列。

当用户想要更改顺序时，我的软件为他们提供了一个拖放界面，最终导致将给定的行插入到其他两行之间（暂时忽略最终情况），并更新所有后续行。

我的第一步是在要插入的行之后增加所有行，因此新项目的序列中有空格。 SQL看起来像这样：

UPDATE menuitem SET ordering=ordering+1 WHERE ordering >= 3; # ordering=3 is where I want to insert the item

我收到以下错误：Error: column ordering is not unique

我猜是因为它试图一次一行地更新行，并且它无法处理这样的事实，即当上一行等于下一行时，将有一瞬间。我对此感到惊讶，因为我认为这将是原子操作，并且仅在查询完成后才考虑约束。

无论如何，假设这是问题所在，我已经尝试（按直觉）以相反的顺序运行更新，因此它将首先更新最高编号，并且不会出现临时冲突。这对我来说似乎很脆弱，但是无论我如何尝试，都还是没有运气：

sqlite> UPDATE menuitem SET ordering=ordering+1 WHERE ordering >= 3 ORDER BY ordering DESC;
Error: ORDER BY without LIMIT on UPDATE

sqlite> UPDATE menuitem SET ordering=ordering+1 WHERE ordering >= 3 ORDER BY ordering DESC LIMIT -1;
Error: column ordering is not unique

sqlite> UPDATE menuitem SET ordering=ordering+1 WHERE ordering >= 3 ORDER BY ordering LIMIT -1;
Error: column ordering is not unique

是什么赋予了？我必须删除唯一约束吗？有没有更好的办法？

Answer 1

您可以通过执行两次更新来执行此操作。这是一个例子：

update menuitem
    set ordering = -ordering
    where ordering >= 3; # ordering=3 is where I want to insert the item

insert new item here

update menuitem
    set ordering = (-ordering) + 1
    where ordering < 0;