PostgreSQLのCHECK制約・外部キー制約は直接修正できない、DROP→ADDし直すしかない

  • PostgreSQL
  • SQL
  • Database

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

PostgreSQLで既存のCHECK制約や外部キー制約の条件を変えたくなって調べた。結論から言うと、直接「修正」するコマンドは無くて、一度DROPしてから新しい条件でADDし直すしかないらしい。

直接修正できない

CHECK制約もFK制約も、ALTER TABLEで条件そのものを書き換えるような構文は存在しない。

-- 既存の制約を削除
ALTER TABLE employees DROP CONSTRAINT chk_salary;


-- 新しい条件で追加
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary >= 1000);

FK制約も同じ。

ALTER TABLE administrators
DROP CONSTRAINT administrators_role_id_fkey;


ALTER TABLE administrators
ADD CONSTRAINT administrators_role_id_fkey
FOREIGN KEY (role_id)
REFERENCES roles(id)
ON DELETE RESTRICT
ON UPDATE CASCADE;

制約名が自動生成されてる場合は\d テーブル名で実際の名前を確認する必要がある。リネームされてることもあるので、推測で決め打ちしない方がいい。

唯一の例外はNOT NULL制約。これだけは直接変更できる。

ALTER TABLE employees ALTER COLUMN salary SET NOT NULL;
ALTER TABLE employees ALTER COLUMN salary DROP NOT NULL;

ON UPDATE CASCADEとON DELETE RESTRICT

FK制約のオプションで挙動が変わる。

  • RESTRICT: 参照されている行の削除・更新を禁止する
  • CASCADE: 参照先の変更/削除に追従して参照元も変更/削除する
  • SET NULL: 参照先が消えたらNULLにする
  • SET DEFAULT: 参照先が消えたらデフォルト値にする
  • NO ACTION: 基本RESTRICTと同じ動作だが、制約チェックのタイミングが違う

例えばrolesテーブルの行がまだadministratorsから参照されている状態で削除しようとすると、

DELETE FROM roles WHERE id = 1;

はこうなる。

ERROR: update or delete on table "roles"
violates foreign key constraint
"administrators_role_id_fkey"

RESTRICTならこれで弾かれる。CASCADEにしてたらadministrators側も一緒に消える。

RESTRICTとNO ACTIONの違い

どっちも「参照されてたら弾く」という点では同じ挙動に見えるけど、厳密には違う。

NO ACTIONは制約がDEFERRABLE INITIALLY DEFERREDで宣言されていれば、チェックをトランザクション終了時まで遅延できる。RESTRICTはこれができない。普段は気にしなくていいけど、同一トランザクション内で一時的に整合性が崩れる操作(順序を変えて後で帳尻を合わせるようなケース)をしたい時は関わってくる。

CHECK制約を大きいテーブルに追加する時

DROP→ADDのADDの部分、対象テーブルが大きいとロックが長く続くことがある。NOT VALIDをつけて追加してから、別途VALIDATE CONSTRAINTする二段階に分けると、既存データの検証を後回しにできてロック時間を短くできるらしい(実機未確認)。

ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary >= 1000) NOT VALID;


ALTER TABLE employees
VALIDATE CONSTRAINT chk_salary;

NOT VALIDの間は新規INSERT/UPDATEには制約がかかるけど、既存行はまだチェックされてない状態になる。