PostgreSQLのSET CONSTRAINTS ALL DEFERREDを書いても外部キーチェックが即時のままだった

  • PostgreSQL
  • SQL

この記事は、構成と文章の整理にAIを使っています。
あくまで読みやすくするための補助で、内容そのものをAIにすべて任せて書いているわけではありませんが、ところどころ相談しながら整えています。

トランザクション内で外部キー制約のチェックを後回しにしたくて SET CONSTRAINTS ALL DEFERRED を使ったが、想定通りに動かなかったのでメモ。

やろうとしたこと

参照先・参照元のテーブルに対して、DELETEの順序を気にせず一括で消したかった。PostgreSQLには制約チェックを遅延させる仕組みがあるので、それを使えば順不同でDELETEしても最後にまとめてチェックしてくれるはず、という想定だった。

BEGIN;


SET CONSTRAINTS ALL DEFERRED;


DELETE FROM tablename;


SET CONSTRAINTS ALL IMMEDIATE;


COMMIT;

起きたこと

SET CONSTRAINTS ALL DEFERRED; を実行しても、DELETEの時点で普通に外部キー制約違反のエラーが出た。DEFERREDにしたはずなのに即時チェックされている状態。

原因

SET CONSTRAINTS はあくまで「制約チェックのタイミングを切り替えるスイッチ」であって、対象の制約自体が DEFERRABLE で定義されていないと何も起きないらしい。PostgreSQLの外部キー制約はデフォルトで NOT DEFERRABLE INITIALLY IMMEDIATE になっているので、単純に ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY ... しただけのテーブルは対象外になる。

効かない例:

ALTER TABLE child ADD CONSTRAINT fk_parent
  FOREIGN KEY (parent_id) REFERENCES parent(id);

効く例:

ALTER TABLE child ADD CONSTRAINT fk_parent
  FOREIGN KEY (parent_id) REFERENCES parent(id)
  DEFERRABLE INITIALLY IMMEDIATE;

DEFERRABLE INITIALLY IMMEDIATE にしておけば、普段は即時チェックのまま、必要なときだけ SET CONSTRAINTS ALL DEFERRED で遅延させられる。

補足

  • SET CONSTRAINTS ALL DEFERRED はトランザクション内でのみ有効。トランザクションが終わると自動的にリセットされるので、次のトランザクションに影響しない。
  • SET CONSTRAINTS ALL IMMEDIATE を単体で実行すると、それまで保留していたチェックがその場で走る。DEFERRED中に一時的に整合性が崩れた状態でIMMEDIATEに戻すと、そこでエラーになる。
  • 上のSQL例、最後が CMMIT; になってるとタイポで構文エラーになる。COMMIT; が正しい。地味にやりがちなので注意。

結論

SET CONSTRAINTS ALL DEFERRED を使いたいなら、対象の外部キー制約を先に DEFERRABLE にしておく必要がある。既存のテーブルで制約を後から DEFERRABLE に変えたい場合は、いったん制約をDROPして DEFERRABLE 付きで再作成することになる（実機での検証はまだしていない）。