主键列的就地重新排序

Question

我有一个带有复合主键的表：

table MyTable 
(
    some_id smallint not null,
    order_seq smallint not null,
    --other columns
)

...其中 some_id 和 order_seq 组成了复合主键。 order_seq 列用于确定 C# 3.5/ASP.NET 应用程序其他部分的显示顺序。

我正在设置一个管理页面，用户可以在其中随机排列这些行，一次更改多行的 order_seq 值：

屏幕上的所有项目都具有相同的 some_id。 当用户单击“提交”时，新的 order_seq 值将显示应该保存到数据库中。

我的问题是 order_seq 是 PK 的一部分，因此整理这些内容的任务变得很复杂。由于 some_id 是相同的，因此除了 order_seq 本身之外，我无法识别它们，因为它会随着我的操作而变化。此外，我必须确保 order_seq 值保持唯一（可能具有某种临时值）。

我最好的想法是使用某种内存中的集合来跟踪我迄今为止所做的更改。不过，我在实施它时遇到了麻烦。如何一次对多行进行就地重新排序？我可以使用基于 C# 或 SQL 的解决方案。

编辑：不幸的是，我对表设计的控制为零，对数据库的控制通常有限。我将无法更改 PK 方案或引入任何新列。

Answer 1

如果可能的话，引入代理主键将为您减轻很多压力。由于您可以更改 order_seq 而不更改任何其他字段，因此它并不真正适合作为主键的一部分（记录中的所有其他字段应直接依赖于主键）。但即使超出了理论知识，您也已经发现了这种复合密钥的技术问题。

如果您确实需要保持结构相同，那么更改关键字段将会很乏味，并且正如您已经发现的那样，需要一些临时值。在我的脑海中，如果我必须完成这个任务，我会分两步完成：

UPDATE Table SET order_seq = @newval + BIG_OFFSET WHERE order_seq = @oldval

UPDATE Table SET order_seq = order_seq - BIG_OFFSET WHERE order_seq > BIG_OFFSET

显然，为每个正在更改的不同行运行第一个语句，然后在最后运行第二个语句以重置它们的值。这应该可以防止您在此过程中的任何时候出现重复的主键。

Answer 2

您需要存储原始复合 PK（隐藏列）。在屏幕上，如果管理得当，两个项目不应该具有相同的顺序。然后，您可以找到具有原始复合 PK 的商品，并使用新的订单值更新它们。

编辑：好吧，您可以在代码中执行类似的操作，即跟踪具有原始 PK 和当前 PK 的对象列表，任何时候用户尝试更改顺序，如果允许，则进行验证，然后允许或阻止它。您需要找到一个具有更新的 Id 和 orderId 的对象，如果您确实找到了一个对象，则不允许这样做。
抱歉，如果我没理解这个问题。试图提供帮助。

Answer 3

您可以使用带有 XML 参数以及一对旧 order_seq 和新 order_seq 的 SP 来完成此操作。

一些测试数据

create table MyTable
(
  some_id int,
  order_seq int,
  name varchar(10)
  primary key(some_id, order_seq)
)

insert into MyTable values
(1, 1, '1_1'),
(1, 2, '1_2'),
(1, 3, '1_3'),
(1, 4, '1_4'),
(2, 1, '2_1'),
(2, 2, '2_2')

存储过程

create procedure SetOrder
  @some_id int,
  @new_order xml
as

;with cte as
(
  select 
    X.N.value('@OldSeq', 'int') as OldSeq,
    X.N.value('@NewSeq', 'int') as NewSeq
  from @new_order.nodes('/i') as X(N)
)  
update T
  set order_seq = C.NewSeq
from MyTable as T  
  inner join cte as C
    on T.order_seq = C.OldSeq
where T.some_id = @some_id

要反转 some_id = 1 的顺序，请像这样调用 SP：

exec SetOrder 1, 
  '<i OldSeq="1" NewSeq="4"/>
   <i OldSeq="2" NewSeq="3"/>
   <i OldSeq="3" NewSeq="2"/>
   <i OldSeq="4" NewSeq="1"/>'

您需要跟踪客户端上的旧 order_seq 并在用户选择提交更改时构建 xml 。