PostgreSQL中删除联合主键指定分区后更新分区序号避免主键重复的实现方法

嘿，这个问题我在实际开发中碰过好几次，联合主键（book_id+part）的场景下更新序号，确实容易踩主键重复的坑，不过只要调整更新逻辑或者用窗口函数就能完美解决！

核心思路：倒序更新避免冲突 #

首先必须用事务包裹所有操作，保证原子性——要么全成功，要么全回滚，不会留下中间错误状态。而避免主键冲突的关键是先更新序号更大的记录，再处理小的，这样就不会在更新过程中出现两个记录共享同一个(book_id, part)的情况。

具体步骤（以MySQL为例） #

1. 先开启事务：

START TRANSACTION;

2. 删除目标记录：

DELETE FROM your_book_table 
WHERE book_id = 1 AND part = 2;

3. 倒序更新剩余记录的part值：
   我们只需要更新那些part > 2的记录（因为删除的是part=2，比它大的序号都要减1），重点是加上ORDER BY part DESC，确保先改大的序号：

UPDATE your_book_table 
SET part = part - 1 
WHERE book_id = 1 AND part > 2
ORDER BY part DESC;

4. 最后提交事务：

COMMIT;

为什么倒序更新靠谱？ #

举个例子：如果我们不排序直接更新，数据库可能按part从小到大执行——先把part=3改成2，这时候其实也不会冲突（因为已经删了原来的(1,2)），但如果是更复杂的场景（比如同时删除part=2和3，剩下1、4、5），倒序更新的优势就体现了：先把5改成3，再把4改成2，全程不会出现主键重复；要是正序改，虽然这次也没问题，但倒序是通用的稳妥方案，不管删多少个中间项都适用。

更灵活的方案：用窗口函数重新生成序号 #

如果你的数据库支持窗口函数（比如MySQL 8.0+、PostgreSQL、SQL Server这些），可以用ROW_NUMBER()直接生成连续的part序号，这种方式更灵活，不管你删了多少个中间part，都能自动修正序号：

START TRANSACTION;

-- 先删除目标记录
DELETE FROM your_book_table 
WHERE book_id = 1 AND part = 2;

-- 用窗口函数重新分配连续的part序号
WITH updated_parts AS (
    SELECT 
        book_id,
        name,
        ROW_NUMBER() OVER (PARTITION BY book_id ORDER BY part) AS new_part
    FROM your_book_table
    WHERE book_id = 1
)
UPDATE your_book_table t
JOIN updated_parts up ON t.book_id = up.book_id AND t.name = up.name
SET t.part = up.new_part;

COMMIT;

这种方式的好处是，哪怕你同时删了part=2和4，剩下的记录也会自动被分配1、2、3的连续序号，不需要手动计算要减多少数值，非常省心。

小提醒 #

• 确保你的事务隔离级别是默认的REPEATABLE READ或者更高，避免在事务执行过程中被其他事务干扰数据。
• 如果是高并发场景，可能需要额外加锁，但普通业务场景用上面的方案就足够稳定了。

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