共通のカラムを持つテーブルの外部キーの設定

質問

MySQLバージョン4.1.16を使用しています。

２つのテーブル「tbl1」と「tbl2」があり、
それぞれ共通のカラムを別のテーブル「common_tbl」に設定（正規化）し
そのcommon_idを「tbl1」と「tbl2」が参照します。
例えば、「tbl2」にデータをinsertする場合に、
まず「common_tbl」からinsertして、そのinsert_idを取得（利用）して
「tbl2」にデータを挿入します。
たぶんこの場合のinsertはこのようなやり方だと思います（あまり自信ないなぁ・・・）
「tbl2」の行を削除した場合に、「common_tbl」の行も自動で削除させたいと思うのですが、
どのように外部キーを設定するのか（どこに設定するのか）が分かりません。
「common_tbl」に「tbl1」のtbl1_idと「tbl2」のtbl2_idを
それぞれ持たせるのはたぶん違うと思いますし・・・
この場合の正しいテーブル構成を含めてアドバイスをお願いしたいです。

例：
「tbl2」のtbl2_idが2の行を削除したら、
「common_tbl」のcommon_idが3の行も削除したい（外部キーで自動的に）

テーブル「tbl1」
+------+---------+----------+
|　tbl1_id|　tbl1_col1　|common_id　|
+------+---------+----------+
|　　1　　|　　　値1　　|　　　1　　　　|
+------+---------+----------+

テーブル「tbl2」
+------+---------+----------+----------+
|　tbl2_id|　tbl2_col1　|　tbl2_col2　|common_id　|
+------+---------+----------+----------+
|　　1　　|　　　値1　　|　　　値1　　|　　　　2　　　|
+------+---------+----------+----------+
|　　2　　|　　　値2　　|　　　値2　　|　　　　3　　　|
+------+---------+----------+----------+

テーブル「common_tbl」
+----------+---------+----------+
|common_id　|　　　col1　　|　　　col2　　|
+----------+---------+----------+
|　　1　　　　　|　　　値1　　|　　　値1　　|
+----------+---------+----------+
|　　2　　　　　|　　　値2　　|　　　値2　　|
+----------+---------+----------+
|　　3　　　　　|　　　値3　　|　　　値3　　|
+----------+---------+----------+

回答No.2

chukenkenkou · ベストアンサー

>主たる（削除したりする場合の優先順位のようなもの）
>テーブルは「医者テーブル」や「教師テーブル」ですよね？
>そうだとしたら、「医者テーブル」のある行を削除したら（ある医者が辞めたら）
>「人情報テーブル」の対応してる行も削除されるというのが普通だと思う

一人で複数の職業を兼業していたり、一時的に無職になったり、転職することもある訳ですから、特定の職業を辞めたからといって、その人の基本情報を削除していいということにはならないのでは？

何を管理することを主目的にしているかにもよりますが、一般的には各人の基本情報を管理するテーブルがあり、そこから職業、資格、扶養家族などを管理するテーブルが関連づく構成にすると思います。

お礼

ご返答ありがとうございます。
たしかに言われてみれば人情報を主にした方が良さそうですね。
その場合、
＞一人で複数の職業を兼業していたり、
は、どのようなテーブル構成になるのでしょうか？
職業が１つだけなら下のような構成でいいのでしょうが、
例えば「人_tbl」の人_idが1の人が、
教師と医者の２つの職業を持っていた場合の構成と
CASCADE方法が知りたいです。

テーブル「人_tbl」
+----------+---------+----------+
|　　人_id　　　|　　　年齢　|　　　職業_id|
+----------+---------+----------+
|　　1　　　　　|　　　32　　　|　　　教師　|
+----------+---------+----------+
|　　2　　　　　|　　　45　　　|パイロット　|
+----------+---------+----------+

テーブル「職種_tbl」
+----------+---------+
|　　職業_id　|　　　年収　　|
+----------+---------+
|　　医者　　　|　2000万　　|
+----------+---------+
|　　教師　　　|　600万　　|
+----------+---------+
|　パイロット　|　1500万　　|
+----------+---------+

補足

すいません、たぶん自己解決しました。
つまり「人_tbl」に職種_idというカラムを設ける必要がなくて
新たに「職業_tbl」というテーブルを作成すれば良さそうです。
なんか勘違いしてました・・・
「職業_tbl」の人_idを外部キーに設定したりというぐあいに。
まだ頭の中が整理しきれてないですが、これらを考えていきたいと思います。

テーブル「人_tbl」
+----------+---------+
|　　人_id　　　|　　　年齢　|
+----------+---------+
|　　1　　　　　|　　　32　　　|
+----------+---------+
|　　2　　　　　|　　　45　　　|
+----------+---------+

テーブル「職種_tbl」（←適切な名前が浮かばない・・・）
+----------+---------+
|　　職種_id　|　　　年収　　|
+----------+---------+
|　　医者　　　|　2000万　　|
+----------+---------+
|　　教師　　　|　600万　　|
+----------+---------+
|　パイロット　|　1500万　　|
+----------+---------+

テーブル「職業_tbl」（←適切な名前が浮かばない・・・）
+-------+-------+-------+
|　　id　　　|　人_id　|　職種_id|
+-------+-------+-------+
|　　1　　　|　1　　　　|　医者　|
+-------+-------+-------+
|　　2　　　|　1　　　　|　教師　|
+-------+-------+-------+
|　　3　　　|　2　　　　|パイロット|
+-------+-------+-------+

回答No.1

auty

試してみてはいませんが、

--------------------------------------------------------------------------------
CREATE TABLE tbl1 (　tbl1_id integer, ...　,common_id integer,
Primary Key (tbl1_id),
Foreign Key (common_id) references common_tbl(common_id)) TYPE = InnoDB;

Primary Keyがない時は、CREATE INDEX ind1 ON tbl1 (tbl1_id);
--------------------------------------------------------------------------------

CREATE TABLE tbl2 (　tbl2_id integer, ...　,common_id integer,
Primary Key (tbl2_id),
Foreign Key (common_id) references common_tbl(common_id) TYPE = InnoDB;

Primary Keyがない時は、CREATE INDEX ind2 ON tbl2 (tbl2_id) ;
--------------------------------------------------------------------------------

たとえば、テーブル「tbl2」に、「FOREIGN KEY 制約」を設けるときに、ON DELETE CASCADE 節を付加できますが、

これは、テーブル「common_tbl」からレコードを削除するときに連動して

テーブル「tbl2」のレコードも削除するというものです。

逆を自動的にやるのは無理だと思います。

・　まず、テーブル「tbl2」から削除して、

・　次に、テーブル「common_tbl」から対応するレコードを削除する
　　（このときもし対応するレコードがテーブル「tbl1」にある場合は失敗する。）

*　　テーブル「tbl2」からも削除してよければ、まさにテーブル「common_tbl」から対応するレコードを削除すれば一発で処理は完了となると思います。

利用するための条件（主キー、インデックス、どちらもInnoDBテーブル型）については、
以下のページを参考にしてみてください。

http://dev.mysql.com/doc/refman/4.1/ja/innodb-foreign-key-constraints.html

http://synth.jp/pg/2007/06/mysql_primary_keyforeign_key.html

お礼

ご返答ありがとうございます。
なるほど、「tbl1」や「tbl2」の方に外部キーを設けるということですか。
ただこの場合の主役（先に操作するテーブル）は「tbl1」や「tbl2」だと思うんです。
なので「common_tbl」から削除して「tbl1」や「tbl2」が
削除されるというのは、なんとなく違和感があって・・・
例えば、「tbl1」や「tbl2」を１つのテーブルで纏めることが
できない（カラム数などに違いがある）と仮定として、

職業情報
「tbl1」＝「医者テーブル」
「tbl2」＝「教師テーブル」
共通の人間
「common_tbl」＝「人情報テーブル」

とした場合、主たる（削除したりする場合の優先順位のようなもの）
テーブルは「医者テーブル」や「教師テーブル」ですよね？
そうだとしたら、「医者テーブル」のある行を削除したら（ある医者が辞めたら）
「人情報テーブル」の対応してる行も削除されるというのが普通だと思うんです。

そもそもこの自分が考えたテーブル構成自体間違っているような気がするのですが、
こういうケースではどのようなテーブル構成、そしてCASCADEさせればベストなのでしょうか？