すべての参照制約を解除したい

質問

SQLのスクリプトで、すべてのテーブルの参照制約を解除して、
いつでも全テーブルを消せるようにすることはできるんでしょうか？

前にカーソルを使ったEXECUTE IMMEDIATE文で無理やりTRUNCATEしようとしたんですが、
参照制約の関係でやっぱりエラーが返ってきました。

どんなテーブル構成でも参照制約をすべて自動解除できればいいのですが・・・。

回答No.1

ossan_hiro · ベストアンサー

(1) USER_TABLESで消したいテーブル名を抽出するカーソルを作成する。

(2)　データディクショナリビューのUSER_CONSTRAINTで制約名(CONSTRAINT_NAME)とテーブル名(TABLE_NAME)を抽出するカーソルを作成する（条件：CONSTRAINT_TYPE='R'）。

(3) (2) のカーソルを使って
alter table テーブル名 disable constraint 参照整合性制約名;
をEXECUTE IMMEDIATE文で実行する。

(4) (1)のカーソルを使って
TRUNCATE TABLE テーブル名;
をEXECUTE IMMEDIATE文で実行する。

(5) (2)のカーソルを使って
alter table テーブル名 enable constraint 参照整合性制約名;
をEXECUTE IMMEDIATE文で実行して元の状態に戻す。

と、こんな感じのPL/SQLを作れば可能ですよ。

＃無駄なフェッチもしてますが、単純なので。
＃truncateしようと思わなければ、deleteで制約関係なしで
＃消せると思いますけど・・・。

回答No.2

sippo06

全テーブル（のデータ？）を消したいとのことですので、
ちょっと方法を変えて、以下のようではいかがでしょうか？
1）テーブル作成用のユーザーを作っておく
2）1）で作ったユーザーの持ち物として全てのテーブルを作成する
3）1）で作ったユーザーの持ち物だけをエクスポートする。
　このとき、表データはエクスポートしないでおく。
で、データを消したいときは
1）のユーザーの削除（このとき、ユーザーの持ち物も一緒に削除してください。）し、再作成後、3）で作ったダンプをインポートすると、
空のテーブルだけがあるデータベースのできあがりです。

お礼

みなさんありがとうございました。

SQLPLUSの本をみながら色々ためしたんですが、結局は
E-R図をみながら親テーブルからDROPしていくのが一番簡単で
わかりやすかったです。

これまでに得たご回答は今後の参考にさせていただきます。