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

HTH,
Markus