firebird - 在 Firebird 中多行中更新具有唯一约束/索引的列

Question

我有一个如下所示的表格:

CREATE TABLE SCHEDULE (
    RCDNO              INTEGER NOT NULL,
    MASTCONNO          INTEGER NOT NULL,
    ROWNO              INTEGER DEFAULT 0 NOT NULL,
    EDITTIMESTAMP      TIMESTAMP
)
ALTER TABLE SCHEDULE ADD CONSTRAINT PK_SCHEDULE PRIMARY KEY (RCDNO);
CREATE UNIQUE INDEX IDX_SCHEDULE_3 ON SCHEDULE (MASTCONNO, ROWNO);

我可以运行这样的查询:

select mastconno, rowno, rcdno, edittimestamp
from schedule
where mastconno = 12
order by rowno desc

我明白了:

由于应用程序代码中的错误，编辑时间戳的时间部分丢失，但这在这里无关紧要。尽管如此，这些记录仍按 ROWNO 的条目降序列出。这就是此表的设计目的。

我试图做的是......

update SCHEDULE
set ROWNO = (ROWNO + 1)
where MASTCONNO = 12

...在准备插入新的 ROWNO=0 记录时，我收到此错误:

attempt to store duplicate value (visible to active transactions) in unique index "IDX_SCHEDULE_3".
Problematic key value is ("MASTCONNO" = 12, "ROWNO" = 3).

顺便说一句，我在 MS SQL Server 中有该表的精确副本，并且那里没有这个问题。这似乎是 Firebird 的工作方式所特有的。

然后我尝试了这个，希望 Firebird 能够以无冲突的顺序将 IN 谓词中的值“馈送到”UPDATE 中。

update SCHEDULE
set ROWNO = (ROWNO + 1)
where MASTCONNO = 12 and 
ROWNO in (select ROWNO from SCHEDULE 
where MASTCONNO = 12 order by ROWNO DESC)

遗憾的是， react 和以前一样。 ROWNO 3 被更新语句复制。

Answer 1

使用唯一索引(MASTCONNO、ROWNO)，我测试了以下内容:

update SCHEDULE
set ROWNO = (ROWNO + 1)
where MASTCONNO = 12
order by ROWNO DESC

工作正常!

update SCHEDULE
set ROWNO = (ROWNO - 1)
where MASTCONNO = 12
order by ROWNO ASC

也能正常工作!

非常感谢马克·罗特维尔。