PostgreSQL『ERROR: update or delete on table violates foreign key constraint』の原因と対処

ERROR:  update or delete on table "parents" violates foreign key constraint "children_parent_id_fkey" on table "children"
DETAIL:  Key (id)=(42) is still referenced from table "children".

□ 親行を削除しようとしたが、子テーブルに参照行が残っている
□ 親の主キー/候補キーを更新したが、FKが ON UPDATE CASCADE でない
□ 子のFK列がNOT NULLなのに、ON DELETE SET NULLを期待
□ 複合FKで一部列だけを変更（MATCHと整合せずNG）
□ 参照側にFK用インデックスがなく、ロック競合や遅延で失敗/タイムアウト
□ トリガ/ビュー/FDW経由の間接更新で一貫性が崩れる

-- 1) 直近エラーの制約名を控える（例: children_parent_id_fkey）

-- 2) 参照している子行を特定
SELECT * FROM children WHERE parent_id = 42;

-- 3) 制約の定義を確認
SELECT conname, confdeltype, confupdtype, condeferrable, convalidated
FROM pg_constraint
WHERE conname = 'children_parent_id_fkey';

-- 4) psqlでテーブル定義
\d children

-- 既存FKをCASCADEへ付け替え（安全確認後）
ALTER TABLE children
  DROP CONSTRAINT children_parent_id_fkey,
  ADD  CONSTRAINT children_parent_id_fkey
    FOREIGN KEY (parent_id)
    REFERENCES parents(id)
    ON DELETE CASCADE;

-- 実行前の影響把握（消える行数を試算）
SELECT COUNT(*) FROM children WHERE parent_id = 42;

-- 削除
DELETE FROM parents WHERE id = 42;  -- 子も連鎖削除

CREATE INDEX IF NOT EXISTS idx_children_parent_id ON children(parent_id);

-- NULLにできるなら
ALTER TABLE children
  DROP CONSTRAINT children_parent_id_fkey,
  ADD  CONSTRAINT children_parent_id_fkey
    FOREIGN KEY (parent_id)
    REFERENCES parents(id)
    ON DELETE SET NULL;  -- parent_id は NULL 許容が必須

-- 既定値に戻すなら
ALTER TABLE children
  ALTER COLUMN parent_id SET DEFAULT 1; -- 例: ダミー親ID
ALTER TABLE children
  DROP CONSTRAINT children_parent_id_fkey,
  ADD  CONSTRAINT children_parent_id_fkey
    FOREIGN KEY (parent_id) REFERENCES parents(id)
    ON DELETE SET DEFAULT;

-- 子の参照先を先に変更（親42→親7へ集約）
UPDATE children SET parent_id = 7 WHERE parent_id = 42;

-- 参照がなくなったら親42を削除
DELETE FROM parents WHERE id = 42;

-- FKを遅延可能に（コミット時に整合性を判定）
ALTER TABLE children
  DROP CONSTRAINT children_parent_id_fkey,
  ADD  CONSTRAINT children_parent_id_fkey
    FOREIGN KEY (parent_id) REFERENCES parents(id)
    DEFERRABLE INITIALLY DEFERRED;

BEGIN;
SET CONSTRAINTS ALL DEFERRED;  -- 以後、このTx内はコミット時に検査
UPDATE children SET parent_id = 7 WHERE parent_id = 42;
DELETE FROM parents WHERE id = 42;  -- コミット時点で整合していればOK
COMMIT;

-- 親の主キー(id)変更に子を追随
ALTER TABLE children
  DROP CONSTRAINT children_parent_id_fkey,
  ADD  CONSTRAINT children_parent_id_fkey
    FOREIGN KEY (parent_id) REFERENCES parents(id)
    ON UPDATE CASCADE;

-- 親の主キーを変更
UPDATE parents SET id = 100 WHERE id = 42;  -- 子のparent_idも100に更新

-- どちらも参照があれば削除失敗
-- RESTRICT: その場で即時拒否
-- NO ACTION: 文の終わり（またはトリガ後）に検査（PostgreSQLの既定）

BEGIN;

-- 1) 実影響件数を把握
SELECT COUNT(*) FROM children WHERE parent_id = 42;

-- 2) 必要なら一時テーブルに退避
CREATE TEMP TABLE backup_children AS
SELECT * FROM children WHERE parent_id = 42;

-- 3) 方針を適用（例：再割当）
UPDATE children SET parent_id = 7 WHERE parent_id = 42;

-- 4) 親の削除
DELETE FROM parents WHERE id = 42;

COMMIT;

-- 参照側FK列にインデックスは必須級
CREATE INDEX IF NOT EXISTS idx_children_parent_id ON children(parent_id);

-- 複合FKなら複合インデックス
CREATE INDEX IF NOT EXISTS idx_child_fk ON child(a_id, b_id);

□ 子テーブルがさらに孫テーブルに参照される（多段連鎖の確認）
□ ビュー/トリガ/ルールによる間接更新が実体を触っている
□ パーティションテーブル間で参照移動（バージョン差異の仕様に注意）
□ NOT VALID なFKが残っており、VALIDATE時に大量違反が露呈
□ 複合FKの一部だけ更新して整合が崩れる

-- 親テーブル 'public.parents' を参照する外部キー一覧
SELECT
  con.conname,
  con.confdeltype, con.confupdtype,
  rel_t.relname AS child_table,
  array_agg(att2.attname ORDER BY att2.attnum) AS child_columns,
  rel_p.relname AS parent_table,
  array_agg(att.attname ORDER BY att.attnum)  AS parent_columns
FROM pg_constraint con
JOIN pg_class rel_p ON rel_p.oid = con.confrelid
JOIN pg_class rel_t ON rel_t.oid = con.conrelid
JOIN pg_attribute att  ON att.attrelid  = con.confrelid AND att.attnum = ANY (con.confkey)
JOIN pg_attribute att2 ON att2.attrelid = con.conrelid  AND att2.attnum = ANY (con.conkey)
WHERE con.contype='f' AND rel_p.relname='parents' AND rel_p.relnamespace = 'public'::regnamespace
GROUP BY con.conname, con.confdeltype, con.confupdtype, rel_t.relname, rel_p.relname
ORDER BY child_table, con.conname;

-- 準備
DROP TABLE IF EXISTS children, parents;
CREATE TABLE parents(id int PRIMARY KEY, name text);
CREATE TABLE children(
  id serial PRIMARY KEY,
  parent_id int NOT NULL REFERENCES parents(id)  -- 既定は ON DELETE NO ACTION
);

INSERT INTO parents VALUES (1,'p1'), (2,'p2');
INSERT INTO children(parent_id) VALUES (1), (1), (2);

-- NG：参照が残っている親の削除
DELETE FROM parents WHERE id=1;
-- ERROR: update or delete on table "parents" violates foreign key constraint "children_parent_id_fkey" on table "children"

-- 解消パターン1：CASCADEに付け替えて削除
ALTER TABLE children
  DROP CONSTRAINT children_parent_id_fkey,
  ADD  CONSTRAINT children_parent_id_fkey
    FOREIGN KEY (parent_id) REFERENCES parents(id) ON DELETE CASCADE;
DELETE FROM parents WHERE id=1;  -- OK（子が連鎖削除）

-- 解消パターン2：再割当（親2に寄せる）
-- （CASCADEにせず）
UPDATE children SET parent_id = 2 WHERE parent_id = 1;
DELETE FROM parents WHERE id=1;  -- OK

□ 影響件数の見積り（SELECT COUNT(*)）
□ トランザクション+一時退避（backup_children等）
□ EXPLAIN/EXPLAIN ANALYZEでコスト把握
□ 子→孫までの連鎖とインデックスの有無
□ タイムアウト/ロック待ち設定（lock_timeout, statement_timeout）の確認

・親を消す → 子も消してよい：ON DELETE CASCADE
・親を消す → 子を残したい：ON DELETE SET NULL/SET DEFAULT（列定義に注意）
・親は消さない → 子を移す：UPDATEで再割当（必要ならDEFERRABLE）
・親キーを更新する：ON UPDATE CASCADE、または不変キーを採用
・性能/安定性：子のFK列にインデックス、順序/境界はトランザクションで管理