如何通过唯一索引约束实现可编辑严格排序且至多n次数据库写入？

核心思路是利用SQL Server中单条UPDATE语句的原子性：在同一个UPDATE语句中计算所有条目的新排序值并统一更新，避免分步更新产生的中间重复值触发唯一约束。这种方案既保留了数据库层面的约束校验，又能将写入次数控制在重排涉及的条目数n以内。

场景1：单条目移动位置 #

假设要将ParentId = @ParentId下的条目@TargetId从原位置@OldPosition移动到目标位置@NewPosition，可通过CTE计算新排序值，一次性完成所有需要调整的条目更新：

WITH OrderAdjustments AS (
    SELECT
        Id,
        OrderNumber,
        CASE
            -- 目标条目直接设为新位置
            WHEN Id = @TargetId THEN @NewPosition
            -- 若原位置 > 新位置：原位置在新位置到原位置之间的条目，排序值+1
            WHEN @OldPosition > @NewPosition AND OrderNumber BETWEEN @NewPosition AND @OldPosition - 1 THEN OrderNumber + 1
            -- 若原位置 < 新位置：原位置在原位置+1到新位置之间的条目，排序值-1
            WHEN @OldPosition < @NewPosition AND OrderNumber BETWEEN @OldPosition + 1 AND @NewPosition THEN OrderNumber - 1
            -- 其他条目排序值不变
            ELSE OrderNumber
        END AS NewOrderNumber
    FROM UserOrderableThing
    WHERE ParentId = @ParentId AND Active = 1
)
UPDATE UserOrderableThing
SET OrderNumber = oa.NewOrderNumber
FROM UserOrderableThing uot
JOIN OrderAdjustments oa ON uot.Id = oa.Id
WHERE oa.NewOrderNumber != uot.OrderNumber;

该语句仅会更新实际需要调整的条目（数量为ABS(@OldPosition - @NewPosition) + 1），完全符合至多n次写入的要求，且因所有更新在同一原子操作中完成，不会触发唯一约束冲突。

场景2：自定义全量排序（用户给出完整顺序） #

若用户直接指定了某ParentId下所有条目的新顺序，可通过临时表存储新排序关系，再一次性批量更新：

-- 临时表存储用户指定的新顺序：Id对应新排序值
DECLARE @NewOrderMapping TABLE (ItemId INT, NewOrder INT);
INSERT INTO @NewOrderMapping (ItemId, NewOrder)
VALUES 
    (@ItemId1, 1),
    (@ItemId2, 2),
    -- ... 按用户指定顺序列出所有条目
    (@ItemIdN, N);

-- 批量更新排序值
UPDATE uot
SET OrderNumber = nom.NewOrder
FROM UserOrderableThing uot
JOIN @NewOrderMapping nom ON uot.Id = nom.ItemId
WHERE uot.ParentId = @ParentId AND uot.Active = 1;

此方法仅需n次写入（n为参与排序的条目数），同样通过原子性更新避免中间冲突，且唯一约束会在所有更新完成后校验最终状态的唯一性。

方案优势 #

• 保留了UQ_UserOrderableThing_ParentId_OrderNumber唯一筛选索引，确保数据一致性，无需依赖应用层校验。
• 无额外冗余写入，写入次数严格控制在重排涉及的条目数以内。
• 无需禁用约束或使用临时值，不存在数据安全风险。

内容的提问来源于stack exchange，提问作者Nitrox Tank