PostgreSQLでのエラー『 table referenced in 』の原因と対処

-- 例: customers を orders が参照
CREATE TABLE customers (id bigint PRIMARY KEY, name text NOT NULL);
CREATE TABLE orders (
  id bigint PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id),
  amount numeric NOT NULL
);

-- NG: 参照されているcustomersをDROP
DROP TABLE customers;
-- ERROR:  cannot drop table customers because other objects depend on it
-- DETAIL: constraint orders_customer_id_fkey on table orders depends on table customers

-- OK1: 参照元テーブルを先にDROP
DROP TABLE orders;
DROP TABLE customers;

-- OK2: 参照元のFKだけ先に外してからDROP
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;
DROP TABLE customers;

-- 指定テーブルを参照しているFKを洗い出す（テーブル名はschema付で）
WITH target AS (
  SELECT 'public.customers'::regclass AS rel
)
SELECT
  con.conname                             AS constraint_name,
  con.conrelid::regclass                  AS referencing_table,
  con.confrelid::regclass                 AS referenced_table,
  pg_get_constraintdef(con.oid)           AS constraint_def
FROM pg_constraint con
JOIN target t ON con.confrelid = t.rel
WHERE con.contype = 'f'
ORDER BY con.conrelid::regclass::text;

-- 参照元FKのDROP文を自動生成
WITH target AS (SELECT 'public.customers'::regclass AS rel)
SELECT 'ALTER TABLE '||con.conrelid::regclass||' DROP CONSTRAINT '||quote_ident(con.conname)||';' AS ddl
FROM pg_constraint con JOIN target t ON con.confrelid=t.rel
WHERE con.contype='f';

-- 実行例（生成したDDLを流す）
ALTER TABLE public.orders DROP CONSTRAINT orders_customer_id_fkey;
DROP TABLE public.customers;

-- 依存する制約やオブジェクトも連鎖的に削除
DROP TABLE public.customers CASCADE;
-- NOTICEで落ちる対象が表示される → 直後にROLLBACKすれば「何が消えるかのプレビュー」に使える
BEGIN;
DROP TABLE public.customers CASCADE;
-- NOTICEを確認
ROLLBACK;  -- 本番前の安全確認として有用

-- DB全体で「誰が誰を参照しているか」を一覧化
SELECT
  con.conrelid::regclass   AS referencing_table,
  con.confrelid::regclass  AS referenced_table,
  con.conname,
  pg_get_constraintdef(con.oid) AS def
FROM pg_constraint con
WHERE con.contype='f'
ORDER BY referencing_table::text, referenced_table::text;

-- 親のDROP手順例（安全側）
ALTER TABLE sales DETACH PARTITION sales_2025q1;
DROP TABLE sales_2025q1;
-- 参照FKを解除後に親DROP
ALTER TABLE some_table DROP CONSTRAINT some_fk;
DROP TABLE sales;

-- 1) 参照先=対象表のFK定義を保存
WITH target AS (SELECT 'public.customers'::regclass AS rel)
SELECT con.conrelid::regclass AS referencing_table,
       con.conname,
       'ALTER TABLE '||con.conrelid::regclass||' ADD CONSTRAINT '||
       quote_ident(con.conname)||' '||pg_get_constraintdef(con.oid)||';' AS add_ddl
INTO TEMP TABLE fk_backup
FROM pg_constraint con JOIN target t ON con.confrelid=t.rel
WHERE con.contype='f';

-- 2) FKをDROPして表をDROP
SELECT 'ALTER TABLE '||con.conrelid::regclass||' DROP CONSTRAINT '||quote_ident(con.conname)||';'
FROM pg_constraint con JOIN target t ON con.confrelid=t.rel
WHERE con.contype='f';

DROP TABLE public.customers;

-- 3) 必要なら後で復元
SELECT add_ddl FROM fk_backup;  -- 出力を実行

DO $$
DECLARE r record;
BEGIN
  FOR r IN
    SELECT conrelid::regclass AS tbl, conname
    FROM pg_constraint
    WHERE contype='f' AND confrelid = 'public.customers'::regclass
  LOOP
    EXECUTE format('ALTER TABLE %s DROP CONSTRAINT %I;', r.tbl, r.conname);
  END LOOP;
END$$;
-- その後 DROP TABLE public.customers;

-- P1: 直接DROP
-- NG: DROP TABLE customers;
-- OK: ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey; DROP TABLE customers;

-- P2: 依存が多すぎる
-- NG: 手作業で探す
-- OK: pg_constraint/pg_get_constraintdef で一覧化→自動生成DDL

-- P3: とりあえずCASCADE
-- NG: いきなり本番でCASCADE
-- OK: BEGIN内でCASCADE→NOTICE確認→ROLLBACK→影響承認→本番適用

-- P4: トリガ無効化で回避できるはず
-- NG: ALTER TABLE ... DISABLE TRIGGER ALL; DROP TABLE ...
-- OK: 依存はカタログ管理。FK解除 or 参照元DROP or CASCADEのいずれか