blog.unresolved.xyz

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

Sat Feb 22 2025

    既存テーブルに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

    参考