--- In firebird-support@yahoogroups.com, Markus Ostenried <macnoz@...> wrote:

>
> On Sat, Mar 14, 2009 at 18:23, woodsmailbox <cosmin.apreutesei@...> wrote:
> >> > I know deferred constraints would solve it, but is there any other way in the absence of it? Assigning an out-of-domain value is not acceptable, since it breaks the domain definition. Thanks.
> >>
> >> Maybe you could swap (update) values of all other columns and leave the
> >> unique column as it is?
> >
> > Twisted :) But I can't, since one of the other columns will always have a PK constraint on it.
> >
> > For example, the ORDER_INDEX column is used for custom ordering of values. I want to be able to move a row's position in the dataset updating that column.
>
> I hope I'm not missing the obvious here, but can't you assign a
> temporary value like so:
>
> procedure swap (pkvalue1 integer, pkvalue2 integer)
> as
> variable oderindex1 integer;
> variable oderindex2 integer;
> begin
> -- read original values
> select order_index from tbl where pk = :pkvalue1 into :oderindex1;
> select order_index from tbl where pk = :pkvalue2 into :oderindex2;
> -- temporarily assign a magic value to one of the records
> update tbl set order_index = MAX_INT where pk = :pkvalue2;
> -- update both records with swapped values
> update tbl set order_index = :oderindex2 where pk = :pkvalue1;
> update tbl set order_index = :oderindex1 where pk = :pkvalue2;
> end
>

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