UNIQUE 制約が付与された値の入れ替え方法
お題はタイトルの通り、UNIQUE 制約がついた行の値を 1 トランザクションで入れ替えたい、と。PostgreSQL のバージョンは 8.4 。例えばこんなテーブルを作り
CREATE TABLE sample (id PRIMARY KEY, value INT UNIQUE);
以下のような状態で、10 と 20 の値を入れ替えたい。
id | value | |
1 | 10 | |
2 | 20 |
結論からいうと、あまりスマートな方法が思いつかず、一旦値を制約に違反しない値 (max(value) + 1 とか、勿論 LOCK かけてから) に事前更新してから、入れ替えるといった泥臭い方法をとらざるを得ない模様。(上手くいかなかった) 対応方法ですが、調べたのは二点。
CASE 文使う方法
ここにある CASE 文を使う方法は、少なくとも PostgreSQL 8.4 環境では以下の通り怒られ。
=> update sample set value = (case id when 1 then 20 when 2 then 10 else value end) where id in (1,2); ERROR: duplicate key value violates unique constraint "sample_value_key"
SET CONSTRAINTS DEFFERED
SET CONSTRAINTS 文で制約チェックを遅延させる方法。ただ、これ PostgreSQL 8.4 では FK 制約にしか使えないようで。ここ にある通り、PostgreSQL 9.0 から UNIQUE 制約でも DEFFERED 出来るようなのですが。。。
他に良いアイディアないかな。。。