外部キー制約参照先テーブルのTRUNCATE

ORACLEでテーブルの切捨て（全データ削除）を行うことができるTRUNCATE文があります。

このTRUNCATE文ですが、外部キー制約（参照制約・参照整合性制約）の参照先テーブルで実行するとエラー（ORA-02266）が発生します。

本記事では、その回避の方法について解説します。

外部キー参照先テーブルのトランケートはNG
エラーの回避方法
1. 外部キー制約の一時的に無効化する
2. 外部キー制約の一時的に削除する
まとめ

外部キー参照先テーブルのトランケートはNG

外部キー制約（参照制約・参照整合性制約）の参照先テーブルでトランケートを実行するとエラー（ORA-02266）が発生します。

※参照元テーブルのトランケートは問題なく実行できます

-- ★外部キー制約（テーブル２のBは、テーブル１のAに存在するデータに限る）

-- 参照先のトランケート
TRUNCATE TABLE test_table1;

ORA-02266: 表には使用可能な外部キーによって参照される一意キー/主キーが含まれています。

エラーの回避方法

しかし、各テーブルを初期化したい場合や、断片化の解消の一環で一度全データを切捨てたい場合など、トランケートを行いたい時があります。

その場合のエラーの回避方法は2つあります。

外部キー制約の一時的に無効化する

1つ目の方法は、外部キー制約の一時的に無効化することです。

トランケート前に無効化し、トランケート後や断片化解消のメンテナンス後に再度有効化します。

文法

-- 外部キー制約の無効化
ALTER TABLE 参照元テーブル名 DISABLE CONSTRAINT 外部キー制約名;

-- 外部キー制約の有効化
ALTER TABLE 参照元テーブル名 ENABLE CONSTRAINT 外部キー制約名;

使用例

-- ★外部キー制約（テーブル２の項目Bは、テーブル１の項目Aに存在するデータに限る）

-- 外部キー制約の無効化
ALTER TABLE test_table2 DISABLE CONSTRAINT fk_test_table2;

-- 参照先テーブルをトランケート
TRUNCATE TABLE test_table2;

-- 外部キー制約の有効化
ALTER TABLE test_table2 ENABLE CONSTRAINT fk_test_table2;

注意点としては、当然のことですが、有効化する際には参照先テーブルに存在しないデータが参照元テーブルに存在しない状態になっている必要があります。

外部キー制約の一時的に削除する

2つ目の方法は、外部キー制約の一時的に削除することです。

トランケート前に削除し、トランケート後や断片化解消のメンテナンス後に再作成します。

文法

-- 外部キー制約の削除
ALTER TABLE 参照元テーブル名 DROP CONSTRAINT 外部キー制約名;

-- 外部キー制約の作成
ALTER TABLE 参照元テーブル名
ADD CONSTRAINT 外部キー制約名
FOREIGN KEY (参照元項目名) REFERENCES 参照先テーブル名 (参照先項目名)
オプション（指定なし／ON DELETE SET NULL／ON DELETE CASCADE）
;

使用例

-- ★外部キー制約（テーブル２の項目Bは、テーブル１の項目Aに存在するデータに限る）

-- 外部キー制約の削除
ALTER TABLE test_table2 DROP CONSTRAINT fk_test_table2;

-- 参照先テーブルをトランケート
TRUNCATE TABLE test_table2;

-- 外部キー制約の再作成
ALTER TABLE test_table2
ADD CONSTRAINT fk_test_table2;
FOREIGN KEY (b) REFERENCES test_table1 (a)
;