> In the absence of deferred constraints, you will not get an elegant solution to this problem.
>
> The suggested approach by Marcus might work with a bit of tweaking. For example, if your order items are all positive integers, you could create a generator and use negative numbers as your temporary holding variable. Your domain would need to allow negative numbers but never use them.
>
> In Markus' example, edit the third query to:
>
> update tbl set order_index = gen_id(gen_negativeorder_index, -1) where pk = :pkvalue2;
>
> This would let it work if two people attempt to swap values (of different sets of records) at the same time. If you can't do that because of the domain definition requires positive integers or because the data may contain negative integers, then you will just need to keep the transactions as brief as possible and allow for retries.
>
> In terms of avoiding server side code, you could do it with execute block in the newer versions of Firebird.
>
> Adam
>

Thanks, Adam, Markus. I've chosen to just drop the constraint and all the swap procedures. To much clutter in the sp namespace for such a simple task. Now I just recompute all ORDER_INDEX values for every reordering operation so reordering will always work even with duplicates.

What I meant by client-side vs server-side code was that I couldn't use the abstractions already present in the client-side libraries, like a grid component with built-in reordering, swapping etc.