Обсуждение: deferrable on unique

table t1: id integer primary key, seq integer not null unique

the seq is for ordering the rows as the user likes.  however, if the
rows are moved around, eg begin update t1 set seq=4 where id=5 update t1 set seq=5 where id=4 end
will bomb because the first update has two rows of seq=4 (although
correct after the transaction).

I thought "deferrable initally deferred" would fix this, but the phrase
is not allowed on unique.

is this correct?  any ideas?


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

It's a low-tech solution but you could:
 begin update t1 set seq=-1 where id=5 update t1 set seq=5 where id=4 update t1 set seq=4 where id=-1 end

This is assuming that you don't naturally have -1 as a valid value of 
that column.

chester c young wrote:

>table t1:
>  id integer primary key,
>  seq integer not null unique
>
>the seq is for ordering the rows as the user likes.  however, if the
>rows are moved around, eg
>  begin
>  update t1 set seq=4 where id=5
>  update t1 set seq=5 where id=4
>  end
>will bomb because the first update has two rows of seq=4 (although
>correct after the transaction).
>
>I thought "deferrable initally deferred" would fix this, but the phrase
>is not allowed on unique.
>
>is this correct?  any ideas?
>
>
>__________________________________________________
>Do You Yahoo!?
>Tired of spam?  Yahoo! Mail has the best spam protection around 
>http://mail.yahoo.com 
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster
>  
>