[pgsql-jp: 27768] Re: 外部キーの設定に関しまして

羽生 章洋 habu @ air-beat.com
2002年 10月 27日 (日) 14:18:16 JST


"msweb" <msweb @ usen-43x235x183x54.ap-USEN.usen.ad.jp>さん:

> はい、その通りです。すいません、・・・、実際に仕事で触っているデー
タは社外秘
> で使えないもので。

いえいえ(^^)

> 1.もし顧客からキャンセルが入ったら(キャンセルの情報は無視するとし
て)
> uriage_jouhouのレコードが削除されて、さらに参照していたuriage_
meisaiのレコー
> ドも合わせて削除。

これについては、既にご理解されていることと思います(^^)
ON DELETE CASCADEですね。

で、次が悩まれているポイントですよね。

> 2.uriage_meisaiのレコードを削除しようとするとuriage_jouhouから参照
されている
> ので処理を行われないようにする。

これを実現してしまうと、タスキがけで制約がループしてしまうことに
なって、親の削除が出来なくなってしまいますよね。

通常は、親を消すにはぶら下がっている子を全部削除しないと親を削除
できません、という風にします。これはON DELETE RESTRICTです。
親を先に削除しようとするとエラーを通知して、「先に子を削除しなさい」
という風にするわけですね。

しかし、上記のいずれであっても、子を削除しようとすると、親がある場合
は削除できなくする、という風にはなりません。

で、何故こうなってしまうのかというと、そもそもの話として、参照整合性
制約を設定してやると、親のない状態で子レコードだけINSERTするというこ
とが出来なくなるからです。これは重要な視点で、今回悩まれているのは、
「削除する」というところに注目が集まっていますが、「そもそも、では
削除する対象はどうやって挿入するのだろうか?」ということを考えたとき
に、上手くいかないことに気付くはずです。

親をINSERTしようとする
 → 子のsales_no,psales_noを参照しなければならないが、存在しない。
  → そこでまず子をINSERTしようとする。
   → 親のpsales_noを参照しなければならないが、存在しない。
    → そこで親をINSERTしようとする・・・(以下続く)

ですので、通常は参照整合性制約では設定せずにアプリケーションとして
組み込むことになります。ここでいうアプリケーションというのはトリガも
含みます。

ただ、どんなユーザインターフェイスを実現するのか、あるいはオペレーシ
ョンを想定しているかは不明ですが、通常はおっしゃるような制約を実現し
てしまうと、にっちもさっちもいかなくなってしまうはずです。

親が消えると子が消える、はずなのに、子を消そうとすると親があるのでエ
ラーになる、わけです。となると、

親をDELETE
 → 親の制約によって子をDELETEしようとする
  → 子の制約が発動してエラー
   → エラーが伝播して親のDELETEが不可能になる
    → 親はDELETEできない
     → 入れたが最後決して消せない

となってしまいます。

恐らくは、「キャンセルとはどういう行為なのか」ということが明確に定義
されないままに、技術的な面だけで解決しようとしているのが問題の核だと
考えます。

私の場合は、ここ数年アプリケーション上でDELETEという処理を行うことは
殆どありません。というのは、キャンセルされたという情報も保持しておき
たいというニーズがあるためです。そこで、使い古された手ではありますが
論理削除ということで、削除フラグというのを設定して、それを見えなくす
るビューをかぶせています。そうすると、これはキャンセルという行為を
UPDATEで実現するということになります。

# あるいは、キャンセル履歴というテーブルを持っていて、そちらと
    JOINして差分を引き出したりもしますが、これもビューです。
    この場合は、INSERTによってキャンセル処理を実現するわけですね。

というわけで、求められている仕様の吟味が必要ではないかな、という
風に考えます。この辺りもう少し情報をいただければ、他の方法も考えられ
るかもしれません。

> 今年の初めから羽生様の書籍で今まで仕事の合間にPOSTGRESQLを勉強中で
> す。

ありがとうございます(^^)

> 他の書籍でまとまったテーブルの資料がなかったので使わせていただいて
> います。

およ?(^^; 恐らく他の書籍の方がこの辺りはいっぱい載っているのでは
ないかな、と思います。

SQLは、他のRDBMSのものであっても結構使えますので、雑食で当たりまわる
方が引き出しが増えますので、PostgreSQLに限定せずに他のRDBMSのものも
当たってみてください。

# あ、MySQLを元々お使いだったんですよね(^^)

できれば、機種依存(^^;でない標準的なSQLの本を入手されることをお薦め
します。単純なリファレンスものではなく、考え方まで含めて触れてあるも
のであれば、尚良いと思います。

# お薦め書籍は、今出てるWEB+DBプレス(Vol.11)の拙稿の末尾を
    ご参考にしてください(^^;    宣伝すんません(^^;;


================================================================
★ワクワク音楽体験サイト それが【エア・ビート・ドットコム】!★
================================================================
有限会社エア・ビート(http://www.air-beat.com/)
取締役兼CEO 羽生 章洋(mailto:habu @ air-beat.com)
================================================================



pgsql-jp メーリングリストの案内