2017/12/07
ORACLEでテーブルの切捨て（全データ削除）を行うことができるTRUNCATE文があります。
このTRUNCATE文ですが、外部キー制約（参照制約・参照整合性制約）の参照先テーブルで実行するとエラー（ORA-02266）が発生します。
本記事では、その回避の方法について解説します。
sponsored link
外部キー参照先テーブルのトランケートはNG
外部キー制約（参照制約・参照整合性制約）の参照先テーブルでトランケートを実行するとエラー（ORA-02266）が発生します。
※参照元テーブルのトランケートは問題なく実行できます
|
1 2 3 4 5 6 |
-- ★外部キー制約（テーブル２のBは、テーブル１のAに存在するデータに限る） -- 参照先のトランケート TRUNCATE TABLE test_table1; ORA-02266: 表には使用可能な外部キーによって参照される一意キー/主キーが含まれています。 |
エラーの回避方法
しかし、各テーブルを初期化したい場合や、断片化の解消の一環で一度全データを切捨てたい場合など、トランケートを行いたい時があります。
その場合のエラーの回避方法は2つあります。
外部キー制約の一時的に無効化する
1つ目の方法は、外部キー制約の一時的に無効化することです。
トランケート前に無効化し、トランケート後や断片化解消のメンテナンス後に再度有効化します。
文法
|
1 2 3 4 5 |
-- 外部キー制約の無効化 ALTER TABLE 参照元テーブル名 DISABLE CONSTRAINT 外部キー制約名; -- 外部キー制約の有効化 ALTER TABLE 参照元テーブル名 ENABLE CONSTRAINT 外部キー制約名; |
使用例
|
1 2 3 4 5 6 7 8 9 10 |
-- ★外部キー制約（テーブル２の項目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つ目の方法は、外部キー制約の一時的に削除することです。
トランケート前に削除し、トランケート後や断片化解消のメンテナンス後に再作成します。
文法
|
1 2 3 4 5 6 7 8 9 |
-- 外部キー制約の削除 ALTER TABLE 参照元テーブル名 DROP CONSTRAINT 外部キー制約名; -- 外部キー制約の作成 ALTER TABLE 参照元テーブル名 ADD CONSTRAINT 外部キー制約名 FOREIGN KEY (参照元項目名) REFERENCES 参照先テーブル名 (参照先項目名) オプション（指定なし／ON DELETE SET NULL／ON DELETE CASCADE） ; |
使用例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- ★外部キー制約（テーブル２の項目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) ; |
まとめ
テーブルの全データ削除できるTRUNCATE文ですが、外部キーが設定してある場合、参照先テーブルのトランケートはエラーになります。
対処法としては、一時的に外部キー制約を無効化するか削除しておく必要があります。
覚えておきましょう。
関連
