update文でcase式を使うとエラーが出る

Question

CREATE TABLE SomeTable
(p_key CHAR(1) PRIMARY KEY,
 col_1 INTEGER NOT NULL, 
 col_2 CHAR(2) NOT NULL);

INSERT INTO SomeTable VALUES('a', 1, 'あ');
INSERT INTO SomeTable VALUES('b', 2, 'い');
INSERT INTO SomeTable VALUES('c', 3, 'う');

UPDATE SomeTable
 SET p_key = CASE WHEN p_key = 'a' THEN 'b'
                  WHEN p_key = 'b' THEN 'a'
                  ELSE p_key END
 WHERE p_key IN ('a','b');

ERROR: 重複したキー値は一意性制約"sometable_pkey"違反となります

1 a 1 あ
2 b 2 い
3 c 3 う

主キーのaとbを入れ替えたいのですがどうしたらいいでしょうか？

Comments

[Zuishin]

これを何度も行うような運用は良くないと思いますが、一度きりですか？

a を d に、b を e に変更し、その後 d を b に、e を a に変更すればできると思います。
これをひとつの問い合わせでできるかどうかは、やってみなければわかりませんが、一度きりのことであれば二回に分けて問い合わせれば良いと思います。

[tanaka964]

学習用のサンプルデータですけど、これがあるのはデータベースに問題があると書いてました
一度に変更できると書いてましたけどあまり使わないほうがいいのですかね
ありがとうございます

[Zuishin]

テーブルを自分で設計しても良いのであれば、遅延制約を使うという手もあります。

https://qiita.com/ariaki/items/9c9cee0cc763964a4ed2

[Orlofsky]

質問は修正できます。
この掲示板ではコードやエラーメッセージは Markdownの[コード]を使ってください。
https://teratail.com/help/question-tips#questionTips37

[Orlofsky]

PRIMARY KEY は変更しない、ってテーブル設計の大原則です。実際に使われるシステムはもっとテーブル数が多いハズ。PRIMARY KEY を変更したら他のテーブルも値の変更が必要になり、修正漏れでトラブルの元になります。

[tanaka964]

ありがとうございます。２つ一気に変更すれば大丈夫と書いてありましたがPRIMARYKEYはいじってはいけないと理解しました。

[Orlofsky]

>２つ一気に変更すれば大丈夫と書いてありました

質問に出典を提示しては？

[tanaka964]

主キーだけでなくユニークキーの値の入れ替えができる。case式の分岐による更新は一気にされるので主キーの重複によるエラーを回避できるとのことです。違うのかな・・・よくわからなくなってきましたね

[tanaka964]

postgreSQLは遅延制約（DEFFERRABLE）オプションをつけることで解決できるとのことです。みなさんいろいろありがとうございました。Oracle,db2,SQL serverでは問題なく実行できるみたいですね

[Zuishin]

解決したのであれば、クローズしてください。
キーを交換するのはアンチパターンなので、自己解決する場合はそれも強調して書いておいてください。

Answer 1

PostgreSQL触ったことないですし動作未検証ですが、一時的にsession_replication_roleをreplicaすればよいのではないでしょうか。
参考：How to swap primary key between records in postgres

Comments

[tanaka964]

参考にします。私と同じエラーのようですね
replicaがよくわからないけど調べます