Db2のTRUNCATEがSQL20356Nで失敗する ― 親表は空にできない理由と回避の手順

Db2 の TRUNCATE TABLE は、外部キーで参照されている親表には実行できません。子表が空でも同じです。この記事の出力は Db2 12.1.4（Linux）で採りました。

まず見る（結論）

db2 => TRUNCATE TABLE TR_P IMMEDIATE
SQL20356N  The table "DB2INST1.TR_P" cannot be truncated because DELETE
triggers exist for the table, or the table is the parent in a referential
constraint.  SQLSTATE=428GJ

テストデータを入れ直す場面なら、子表 → 親表の順に空にして、親表は NOT ENFORCED を挟んで TRUNCATE するのが手早いです。

TRUNCATE の書き方

TRUNCATE TABLE 表名 IMMEDIATE
TRUNCATE TABLE 表名 REUSE STORAGE IMMEDIATE                         -- 領域を表に残す
TRUNCATE TABLE 表名 DROP STORAGE IGNORE DELETE TRIGGERS IMMEDIATE  -- 領域を返す・DELETE トリガーがあっても実行

IMMEDIATE は省略できません。TRUNCATE TABLE TR_P だけだと SQL0104N An unexpected token "END-OF-STATEMENT" ... Expected tokens may include: "IMMEDIATE" になります。マニュアルでは、IMMEDIATE 付きの TRUNCATE は取り消せない操作とされています。自己参照の外部キーを持つ表（同じ表の中で親子になっている表）は、親表扱いにならず TRUNCATE できました。

SQL20356N の原因は「DELETE トリガーがある」か「参照制約の親である」のどちらかです。DELETE トリガーが原因のときは、既定の RESTRICT WHEN DELETE TRIGGERS の代わりに IGNORE DELETE TRIGGERS を指定します。上の結果は外部キーだけの表で採っています。

回避の手順

外部キーを NOT ENFORCED にする

TRUNCATE TABLE TR_C IMMEDIATE;                           -- 子表を先に空にする
ALTER TABLE TR_C ALTER FOREIGN KEY FK_TR NOT ENFORCED;
TRUNCATE TABLE TR_P IMMEDIATE;                           -- 通る
ALTER TABLE TR_C ALTER FOREIGN KEY FK_TR ENFORCED;

制約を残したまま検査だけ止めます。子表に行があるまま親表を空にすると、親の無い行が残ります。

外部キーを外して付け直す

ALTER TABLE TR_C DROP CONSTRAINT FK_TR;
COMMIT;
TRUNCATE TABLE TR_P IMMEDIATE;
ALTER TABLE TR_C ADD CONSTRAINT FK_TR FOREIGN KEY (P_ID) REFERENCES TR_P(ID);

子表に行が残っていると、付け直しは SQL0667N The FOREIGN KEY "FK_TR" cannot be created because the table contains rows with foreign key values that cannot be found in the parent key of the parent table. で失敗します。付け直す時点で子表の中身が検査されるので、こちらは不整合が残りません。付け直す前に制約の定義（ON DELETE の指定など）を db2look -d sample -e -t TR_C で控えておきます。

DELETE で空にする

子表に行が残っていると SQL0532N A parent row cannot be deleted because the relationship "DB2INST1.TR_C.FK_TR" restricts the deletion. になります。外部キーのエラーの読み方は 外部キー制約エラーの逆引きに書きました。

20万行（1行約100バイト）の表で MON_GET_TRANSACTION_LOG の LOG_WRITES（書いたログページ数）を比べると、DELETE は 8,625 ページ、TRUNCATE は 9 ページでした。大きい表で DELETE を使うときは、ログがいっぱいになる SQL0964C に注意します。

LOAD REPLACE で空にした場合

空ファイルを LOAD FROM /dev/null OF DEL REPLACE INTO TR_P NONRECOVERABLE で読み込むと、親表は空になりますが次の状態が残りました。

• 親表 TR_P は SET INTEGRITY ペンディング（SYSCAT.TABLES.STATUS が C）。SELECT は SQL0668N Operation not allowed for reason code "1"
• 子表 TR_C は STATUS が N（正常）のまま、親の無い行（P_ID = 1）が残っていた

空にする目的で LOAD を使うのは避けます。

関連: Db2で親子の表に追加・削除できない ― 外部キー制約エラーの逆引き ／ MySQLのテーブル削除 ― DROP・TRUNCATE・DELETEの違い

この記事のような現場のTIPSを、Db2運用の全体像として12章に体系化しました。公式マニュアルには載らない「あのとき知っていれば」を減らすための1冊です。