PostgreSQLで検証を無効にしてFKを設定する方法

既存テーブルにFKを追加しようとしたときにDBに負荷がかかるのを避けたいので、検証なしのFKを張る方法をメモします。

こんな感じで NOT VALID をつけておけばよいです。

1ALTER TABLE "SomeTable" ADD CONSTRAINT "SomeTable_fkey" FOREIGN KEY ("someId") REFERENCES "ToTable"("id") ON DELETE RESTRICT ON UPDATE CASCADE NOT VALID;

検証がずっと無効だと困るので、別途検証を有効にします。

1ALTER TABLE "SomeTable"  VALIDATE CONSTRAINT "SomeTable_fkey";

テーブルのON DELETE、ON UPDATEの確認

1select
2  connamespace,
3  conname,
4  c.relname as child_table,
5  p.relname as parent_table,
6  confdeltype
7from
8  pg_constraint
9  join pg_class c on c.oid = conrelid
10  join pg_class p on p.oid = confrelid;
11-- Foreign key deletion action code: a = no action, r = restrict, c = cascade, n = set null, d = set default

参考

• PostgreSQL: Documentation: 17: 51.13. pg_constraint
• sql - How to know if a foreign key has cascade on delete clause - Stack Overflow