リレーションシップと外部キー制約について

■最終的にやりたいこと
・なるべくコード(SELECT文など)を見ずに、「DB」「テーブル定義者」「ER図」等からテーブル間の関係性を把握したい


■具体例
・投稿一覧。「userテーブル」「postテーブル」
・「postテーブル」の「user_id」カラムは、「userテーブル」の「id」カラムに対応
※簡易な場合はある程度想像は付くのですが、ちょっと複雑な構成になると途端に苦労するので、何か良い方法はないかと思い、質問しました


■質問
◆「リレーションシップを組む」際、「外部キー制約」はかけるのでしょうか？
例えば、上記「投稿一覧」DBを構築する際では、どうするのでしょうか？
1.普通、「外部キー制約」をかける
2.普通、「外部キー制約」をかけない
3.どちらでも良い

◆「外部キー制約」は何の為にかけるのでしょうか？
・「SELECT＆JOIN」でデータ取得出来るのであれば、「外部キー制約」と「リレーションシップ構築」に関係性はないと思うのですが、そういう認識で合っているでしょうか？
・参照先データが削除されたら整合性がとれなくなる場合のみかけるものでしょうか？

◆「リレーションシップを確認」する目的で、「外部キー制約」をかけても良いのでしょうか？
・「データ削除の整合性」ではなく、「リレーションシップを確認」する目的で外部キー制約」をかけても良いのでしょうか？

◆「外部キー制約」以外に、「リレーションシップを確認」する方法はあるのでしょうか？
・コード(SELECT文など)を見ずに、テーブル間の「リレーションシップを確認」する方法としては、「外部キー制約」以外に何かあるのでしょうか？
・そもそも、「外部キー制約確認」＝「リレーションシップ確認」という考えは正しいのでしょうか？

No.3 ベストアンサー 回答者： mitoneko 回答日時： 2012/07/21 00:04

　えらく、外部キー制約に拘っているように見えますが、外部キー制約は、テーブル間の関係の一例でしかありません。

　「最終的にやりたいこと」の文章がすごく象徴的です。
　というのは、完成したＥＲ図というのは、実体に対して、これをデータベースでどのように表現したかを全て記した図です。　この図には、全てのテーブル・その属性・そしてテーブル間の関係が全部表現されています。（ていうか、全部表現されていなければ、そのＥＲ図は未完成です。）
　省略せずに全てが書かれたＥＲ図を見れば、テーブル定義書は機械的に完成しますし、ＤＢの実体もやはり機械的に完成します。

　テーブル間の関係が少し複雑になってくると、関係が見えなくなる。理解できなくなるのは誰でも同じです。これをわかりやすく整理して、誰にでも（設計している途中の自分にも）紛れなく解るようにするためにＥＲ図を書く。
　これがＥＲ図の目的です。
　（間違っても、クライアントの要求に応じて、仕方なく、システムが全部完成した後で、完成図書を作成する事務作業の一部として作成する物では断じてありません。）
　
　さて、こうして書かれたＥＲ図があるのに・・・「「ER図」等からテーブル間の関係性を把握したい」とはどういうことか？
　少なくとも、設計作業の一部としてちゃんと作成したＥＲ図があれば、これを見てテーブル間の関係が解らないなんてあり得ません。（本当に解らなければ、それはＥＲ図が間違っています。）
　複雑すぎて、ＥＲ図が理解できないというのなら解ります。その場合は、きっと、何を見ても理解できないでしょう。もはや解っている人に、実体の説明からレクチャーしてもらうしかありません。

　さて、外部キー制約とは、外部キーの要件を満たす様にデータが入力されることを、システムとして強制するために作成します。（ＮＵＬＬが絶対に入っていないことをシステムとして強制するためにＮＯＴ　ＮＵＬＬ制約をつけるのと全く同じです。）
　したがって、外部キーで無いものに外部キー制約をかけてはいけません。
　また、外部キー制約はデータ削除の整合性を取るためにつけるものでもありません。外部キー制約をつけたテーブルで、データが削除された時に外部キーの要件を満たさなくなるデータを自動的に連鎖削除するオプション機能はありますが、これは単なる便利機能です。
　本来の機能は、ＮＯＴ　ＮＵＬＬ制約の付いた要素をＮＵＬＬにしようとすると、エラーではじかれるのと同じで、外部キー制約を満たさなくなる追加・更新・削除の操作をしようとするとエラーではじかれることになります。

＞・コード(SELECT文など)を見ずに、テーブル間の「リレーションシップを確認」する方法としては、「外部キー制約」以外に何かあるのでしょうか？
　
　このコードを書く時に、テーブル間の関係を理解するために、ＥＲ図を書いておくんです。ＥＲ図がなかったら、そもそも、このコードが書けません。（まぁ、世の中には、ＥＲ図は制作者の頭の中だけに存在するという事例は山とありますけどね（苦笑））
　最終目的に対して、質問が本末転倒になっているのがわかりますか？

　

この回答への補足

回答ありがとうございました。
＞外部キー制約は、テーブル間の関係の一例でしかありません
＞最終目的に対して、質問が本末転倒
＞ＥＲ図には、全てのテーブル・その属性・そしてテーブル間の関係が全部表現されています
勉強になりました

＜今回の質問背景＞
・ER図存在しない
・ツールを利用して、DBからのER図自動生成を試す(「MySQL Workbench」と「Mk-2」)
・何れのツールも、外部キー制約がある場合のみ、関係を示す矢印が自動生成された
・逆に、外部キー制約を書いていないDBは、関係性を示す矢印が生成されない ← 勘違いかもしれませんが…
・「リレーションシップ」には、「外部キー制約」が必要かも！ と思い、質問
という流れでした


追加で1点教えてください。
ＥＲ図は、普通どうやって作成するのでしょうか？

◆順番
1.「ER図」作成した後、作業開始
2.平行作成
3.作業後に、「ER図」作成
※いきなり「ER図」を作成するのか、DB見ながら作成するのか、知りたいです

◆ツール
・「ER図」作成する際、よく使うツールがあれば教えてください

◆ER図自動生成
・DBからER図を自動生成したりするでしょうか？
・その場合、全部をツール任せで行なうのでしょうか？ あるいは一部利用するのでしょうか？

補足日時：2012/07/21 13:13

No.7 回答者： mitoneko 回答日時： 2012/07/25 22:36

＞調べている内、「現在のDB設計やリレーションが本当に適切なのか(そもそもリレーション設定とは、DB構築時に行なうべきものか、SQLを走らせる度に構築するものか、など)」

　ＥＲ図の逆生成にかんしては、Ｎｏ．５　６で答えておられるとおり。
　ただ、この引用部分が目的なら、システムの要件をもう一度洗い直す必要があります。（要件そのものが、現在あるシステムが作られた時と変わっている可能性もありますけどね。）
　ちゃんと要件が精査され、ちゃんと設計が行われていれば、テーブル（エンティティー）間の関係は、設計中に全部現れているはずなんです。外部キー制約に表現できるリレーションのみならず、外部キーに表現できないリレーションも含めてです。

　さて、現在のＤＢ設計が正当かを評価しようと思ったら、当然ですが、あなた自身が、正しいと思うＥＲ図を要件から起こしておく必要があります。でないと、現状のＤＢが正しいかどうか判断できないですよね。（まぁ、自分で作ったＥＲ図が正しいかどうかという問題もありますが、これを言い出すと、「私は現在のシステムを評価する資格があるのか？」と問い出すことになりますので、横に置きます。）

　これが前提であれば、エンティティーや属性の意味（これは、あくまで実体の業務の上での意味です。）は、理解できているはずですから、現在のＤＢがあれば、基本的なＥＲ図は起こせます。
　でも、外部制約に現れないリレーションがどう表現されたかまでは見えません。このような関係を含め、このＤＢが正しく扱われているかは、ＳＱＬ・アプリケーション側の責任ですから、こればかりはアプリケーションを見ないと何ともならないです。

この回答へのお礼

回答ありがとうございました。

＞現在のＤＢがあれば、基本的なＥＲ図は起こせます。
＞でも、外部制約に現れないリレーションがどう表現されたかまでは見えません。このような関係を含め、このＤＢが正しく扱われているかは、ＳＱＬ・アプリケーション側の責任ですから、こればかりはアプリケーションを見ないと何ともならないです
大変参考になりましたー


＜回答いだたいたみなさんへのお礼＞
・当初の目的は、「ＥＲ図逆生成」させたかっただけなのですが、
調べていく内分からないことが出てきたので、かつ、
ネットで調べてもよく分からなかったこともあり、
また、回答いただいた方々が詳しそうだったので、
この機会を逃したくないと思い、
追加で色々質問させていただきました。

おかげで、疑問を解消することができました。

「mitoneko」さん、「jjon-com」さん、
お忙しい中、何度も回答いただき、ありがとうございました！

お礼日時：2012/08/08 15:31

No.6 回答者： jjon-com 回答日時： 2012/07/23 08:41

・既存のDB／テーブル定義書に必要となる全属性が不足なく含まれている。

・DB／テーブル定義書の表名や属性名から，他の表や属性との関係が把握できる。

のであれば，プログラムやSQL文がなくても適切なER図は作成できます。
（それをするのがシステムエンジニアの仕事です）

既存のDB／テーブル定義書を参照するだけではその確証がない，すなわち詳細な仕様が不明だというのであれば，既存情報を元にしたER図は作成できますがそれがシステムにとって適切なER図であるかどうかは分かりません。

この回答へのお礼

回答ありがとうございました。

＞詳細な仕様が不明だというのであれば，既存情報を元にしたER図は作成できますがそれがシステムにとって適切なER図であるかどうかは分かりません
参考になりましたー

お礼日時：2012/08/08 15:14

No.5 回答者： jjon-com 回答日時： 2012/07/23 02:13

JavaやRubyで文法的には間違っていないコードが書けることと，オブジェクト指向の考えが反映されたJavaやRubyのコードが書けることとは別問題ですよね。

同様に，ER図の書式的には間違っていない作図が描けることと，データモデリングの考えが反映されたER図が描けることは別問題なんです。

----------------------------------------
> DBからER図を自動生成したりするでしょうか？

はい，質問者ご自身がお試しになったようにそういうツールがあります。
ただしそれはDBの内容をER図の書式的に間違っていない作図に変換したというだけで「そのテーブル設計が理想的なものであるということを示す保証も証拠も存在しない(ANo.2)」です。

同様に，JavaやRubyのソースコードからクラス図を自動生成するツールもありますよ。
ただしそれはソースコードの内容をクラス図の書式的に間違っていない作図に変換したというだけで，元のコードのそのクラス設計がオブジェクト指向の考えとしてふさわしいものであるということを示す保証はなにもないです。

----------------------------------------
> いきなり「ER図」を作成するのか、DB見ながら作成するのか、

いきなりER図を作成します。DBは，ER図から導き出される結果であり，ER図を作成している時点ではDBは存在していません。

----------------------------------------
> ＥＲ図は、普通どうやって作成するのでしょうか？

データモデリングの勉強を通じて適切なER図が描けるようになります。
オブジェクト指向の勉強を通じて適切なクラス図が描けるようになるのと同様です。

----------------------------------------
----------------------------------------
ここまで回答してきて，質問者の問題意識と私の回答にズレがあるように思いました。

質問者は，現在のDB設計やリレーションが本当に適切なのかどうか検討したいわけじゃなく，
現在のDB設計やリレーションは適切であるという前提の上で，テーブル間の関係性を把握したいだけなのですよね。
であるなら，リレーションシップにはすべて外部キー制約が指定されている，外部キー制約が指定されていない属性はリレーションシップに関係していない，という理解で良いでしょう。現在のDB設計やリレーションは適切，なのですから。

この回答への補足

回答ありがとうございました。

＞質問者は，現在のDB設計やリレーションが本当に適切なのかどうか検討したいわけじゃなく，
＞現在のDB設計やリレーションは適切であるという前提の上で，テーブル間の関係性を把握したいだけなのですよね。
一番の目的は、「テーブル間の関係性を把握したい(リレーションをDBからのみ判断して再現出来るか？)」ですが、調べている内、「現在のDB設計やリレーションが本当に適切なのか(そもそもリレーション設定とは、DB構築時に行なうべきものか、SQLを走らせる度に構築するものか、など)」疑問に思えてきたので、質問しています。

理解を深める上で一点教えてください。
4さんの補足に書いたのですが、(イレギュラーなケースだとは思いますが)
「仕様不明」かつ「プログラム閲覧不可」かつ「DB／テーブル定義書のみ閲覧可能」な状態では、
ER図作成不可、という認識で合っているでしょうか？

補足日時：2012/07/23 07:55

No.4 回答者： mitoneko 回答日時： 2012/07/21 20:19

＞ＥＲ図は、普通どうやって作成するのでしょうか？

　普通は、要件の定義が終わった後、一番に作成します。
　このＥＲ図に従って、テーブル定義書を作成し、その後、ＤＢ上にテーブルその他のオブジェクトを作成するものです。
　いくら検証しておいても、システムを作っていくにしたがって、やっぱり修正は出ます。そのたびに、ＥＲ図も修正します。

　私は、専用ソフト使わないもので、残念ながら紹介も出来ません。

　使ったことはありませんが、多分、既存のＤＢから自動解析でＥＲ図を作っても、不完全な物しか出来上がらないはずです。

　本来、既存のＤＢから逆に解析してＥＲ図を作成しようとしたら、
　　・実体に関する知識がちゃんとあり、テーブルを見ただけで、その関係が把握できる。
　　・使用されているプログラムを全て読み、テーブルの使用方法を解析する。（ＳＱＬだけにとどまりません。ＳＱＬを使用する汎用言語内で、まだ追加の処理をしている可能性があります。）さらに、ＳＱＬをダイレクトに使用しているユーザーがいる場合、そのＳＱＬも必要ですね。

　どちらも、自動解析ソフトには不可能です。前段なら、ＳＦ小説級の人工知能が必要ですし、後段なら、そのソフトに全てのアプリケーションを入力する必要がありますが・・・多分、どちらも出来ないでしょうから。解析ソフトに出来るのは、せいぜい、ＤＢに定義されたテーブル情報から解ることをリストアップできるだけです。
　外部キーに関しては、制約を定義するのでかろうじてリレーションとして線を引けますが、出来るのはそこまでです。
　当然、この後の作業は人間がする必要があります。
　まぁ、テーブルと属性のリストを作ってくれるだけでも、作業的には随分助かりますけどね。

この回答への補足

回答ありがとうございました。

＞要件の定義が終わった後、一番に作成
＞このＥＲ図に従って、テーブル定義書を作成し、その後、ＤＢ上にテーブルその他のオブジェクトを作成
やろうとしていることが、本来の流れと逆だと言うことがよく分かりました。

分かったのですが、自分の認識が合っているか確認したいので、以下のケースについて教えてください。
＜前提＞
・仕様不明
・ER図ない
・プログラム自体の閲覧不可
・DB／テーブル定義書は閲覧可能

このとき、リレーションシップを伴ったER図を作成することは不可能、と考えて良いでしょうか？
(理由は、「プログラム」もしくは「SQL文」でのみ利用している「リレーション」が、不明なため…)

つまり、ＥＲ図を作成するためには、下記何れかが必須
・仕様を把握
・「DB」「プログラム」「SQL文」、全て閲覧可能
※テーブル定義書は、参考になるけど、リレーションシップとは直接関係なし

こういう認識で合ってるでしょうか？

補足日時：2012/07/23 07:36

No.2 回答者： jjon-com 回答日時： 2012/07/20 15:51

◆「外部キー制約」以外に、「リレーションシップを確認」する方法はあるのでしょうか？

ER図を参照してください。ER図がなければ作成してください。

ご質問の内容はすべて本末転倒なんです。

> ※簡易な場合はある程度想像は付くのですが、
> ちょっと複雑な構成になると途端に苦労するので、

ということであるなら，テーブルだの制約だのを云々するより先に，ER図を作る必要があるということです。
実体は何か，実体がもつ属性は何か，多重度はどうなっているか，それを踏まえて新たな実体を設ける必要性が出てきたか。リレーションシップは意識的に組むものでもなく，ER図に自然と表れてくるものです。リレーションシップは確認しなくても，ER図にすでに表れています。
ER図が無いのに，テーブル定義書やテーブル自体やコードが存在するということは，そのテーブル設計が理想的なものであるということを示す保証も証拠も存在しないということです。

----------------------------------------
◆「リレーションシップを確認」する目的で、「外部キー制約」をかけても良いのでしょうか？

適当な属性間に外部キー制約をかけた状態でしばらく稼働させておいてたまたま異常が発生しなかったからといって，それがそもそも現状の表分割が適切であるかという保証にはならず，ひいてはそのリレーションシップが適切であるかどうかという保証にもなりません。

----------------------------------------
◆「外部キー制約」は何の為にかけるのでしょうか？

外部キー属性の取りうる値は，一般的な属性よりも制約があるからかけます。

何の為？と改めて質問されてしまうと，逆にこう問い返してみたくなります。

「変数のデータ型は何の為にあるのでしょう？　間違ったデータを格納したら整合性がとれなくなる場合のみ必要なだけで，人間が正しく値を与えれば不要なのでしょうか」
「コンパイラの構文チェックは何の為にあるのでしょう？　間違ったコーディングをしたら問題がある場合のみ必要なだけで，人間が正しくコーディングすれば不要なのでしょうか」

人間が正しくそれをおこなうなら不要，というのはまあその通りなのでしょうけれど，人間がいつも絶対に正しくそれをおこなうという保証がないから，高速かつ正確なコンピュータで自動検査をさせた方が楽で効率的なわけです。外部キー制約だって同じですよ。

----------------------------------------
◆「リレーションシップを組む」際、「外部キー制約」はかけるのでしょうか？

その属性が外部キーであるなら外部キー制約をかけます。理由は前述しました。

----------------------------------------
> 最終的にやりたいこと
> ・なるべくコード(SELECT文など)を見ずに、
>「DB」「テーブル定義者」「ER図」等からテーブル間の関係性を把握したい

ということで，ER図があればテーブル間の関係性を把握できます。
そのER図に描かれているのが実体と関連と多重度だけであり，属性の記載がないのなら，テーブル定義書も併せて参照してください。

この回答への補足

回答ありがとうございました。

3さんの補足に書いたのですが、一般的に、「ER図」ってどうやって作成するのでしょうか？
もしよければ、教えてください

補足日時：2012/07/21 13:16

No.1 回答者： lv4u 回答日時： 2012/07/20 13:42

>>◆「リレーションシップを組む」際、「外部キー制約」はかけるのでしょうか？

3.どちらでも良い。
ただ、RubyonRails(RoR)の作法に従って作成する場合は、作成しないとエラーにたぶんなる。

>>◆「外部キー制約」は何の為にかけるのでしょうか？

まあ、あったほうが処理が速くなるし、わかりやすくなるから。
RoRの場合は、処理上必要だからという答えになるかも？

>>◆「リレーションシップを確認」する目的で、「外部キー制約」をかけても良いのでしょうか？

いいんじゃないですか？もちろん、目的に合致するプラス面もあれば、なんらかのマイナス面も生まれると思いますが。

>>◆「外部キー制約」以外に、「リレーションシップを確認」する方法はあるのでしょうか？

あれば、ＥＲ図を見る。なければ、テーブル定義書みたり、コードを追いかけることになると思います。
以前、アプリを作成した時、メンバーがもっとも参照していた資料はＥＲ図でした。
なので、面倒でもＥＲ図を作成・メンテすることは大切だと思います。

この回答への補足

回答ありがとうございました。

＞以前、アプリを作成した時、メンバーがもっとも参照していた資料はＥＲ図でした。
＞なので、面倒でもＥＲ図を作成・メンテすることは大切だと思います。
なるほど、ＥＲ図ってとっても重要なのですね。

ちなみに、3さんの補足に書いたのですが、「ER図」ってどうやって作成したりメンテしたりするのでしょうか？　よく使うツールとか、初心者向けのコツとかあれば、教えてください

補足日時：2012/07/21 13:19