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