【PostgreSQL】一時的に UNIQUE 制約を違反してしまう場合の回避

はじめに

order という順序を司るカラムがあったとします。
このとき order には NOT NULL 制約 と UNIQUE 制約 がかかっているとします。

id	order
1	1
2	2
3	3

↑を↓のように更新したいとします。要は並び替え操作みたいなものですね。

id	order
1	2
2	3
3	1

このとき、まずは id が 1 のレコードの order を 2 に更新しようとします。

すると、すでに order が 2 のレコードが存在するため、 UNIQUE 制約に引っかかってしまいますね。

NOT NULL 制約を削除すれば、一旦全て NULL に更新 → 新たに order を設定みたいなことはできますが、

データモデル的には NOT NULL も UNIQUE 制約 もできれば外したくないです。

こういった一時的に UNIQUE 制約を違反してしまう時にどうするかについて書いていきたいと思います。

SET CONSTRAINTS を使う

SET CONSTRAINTS コマンドは トランザクション内で制約の検査が行われるタイミングを設定できます。

UNIQUE 制約の作成時に SET CONSTRAINTS が有効になるように設定しなければならないので
まずは migration ファイルを作るところから。

まだテーブルを作っていない方はテーブル作成の時に、
すでにテーブルを作ってる方は一旦今の UNIQUE 制約は　DROP して以下の方法で新たに作り直しましょう。

対象の UNIQUE 制約を設定する時に後ろに DEFERRABLE をつけます。
DEFERRABLE は 遅延可能なという意味です。

これによって SET CONSTRAINTS コマンドでトランザクション内で制約検査のタイミングを指定することが可能です。
今回は DEFERRED を使うことででトランザクションの最後に制約検査のタイミングを指定します。

migrationのSQL

ALTER TABLE 対象テーブル ADD CONSTRAINT 制約名(任意) UNIQUE (column, ・・・) DEFERRABLE;

以下が実際に update を実行する時の SQL です。
このように書くことで、途中でユニーク制約に引っかかってしまうような更新でも、制約の検査はトランザクションの最後で行われるので大丈夫です。

updateのSQL

BEGIN;
  SET CONSTRAINTS 対象の制約名 DEFERRED;
  /* 以下は通常通りの update の SQL を書いていく */
COMMIT;

参考文献