Continuous Diggin'

プログラミングと音楽のトピックを中心に書いてます

mysql workbenchでDDLを作成する(外部キー制約は使わない)

外部キー制約について

定義

リレーショナルデータベース(RDB)で、テーブルのある列に、別のテーブルの特定の列に含まれる項目しか入力できないようにする制約。また、その際に指定する列。 例えば、受注テーブルの顧客コードの列に、顧客テーブルの顧客コードの列を外部キーとして設定すれば、顧客テーブルに登録されていない顧客コードを受注テーブルに誤って入力してしまうことを防ぐことができ、既存顧客から選択して入力するような形にすることもできるようになる。

IT用語辞典より

つまり、外部キーを使うことでテーブル間のリレーションが妥当であることを保証している。

InnoDBによる外部キー制約

詳しくは公式ドキュメントにあるが、重要な点は、

  • 外部キー制約をするためには、親と子のテーブルがどちらもInnoDBである必要がある。

  • 親テーブルのキーカラムに変更(更新、削除)があった場合にとる処理として、いくつかのオプションが有る。 簡単に言うと、同じキーである子テーブルの該当するカラムについて、

    • "削除(CASCADE)"
    • "NULL値へ更新(SET NULL)"
    • "更新を無視(NO ACTION=RESTRICT)" を設定できる。
  • テーブルスキャンを必要としないよう、外部キーと参照キー上にインデックスが必須。外部キー上のインデックスは自動的に作成される。

MySQL Workbench

MySQL Workbench

Oracleが提供しているGUIツール。MySQLをマネジメントするための多様な機能を提供している。今回は、ER図作成機能、ER図からのDDL自動作成機能について利用する。 基本的な利用方法をざっと見るには、http://promamo.com/?p=1979http://jutememo.blogspot.jp/2010/10/mysql-workbench.html が参考になる。

リレーションの表現

Mysql Workbenchではリレーションを表現する方法として、実線アイコン(=identifying relationship 以下、実線)と点線アイコン(=non-identifying relationship 以下、点線)とがある。この2つの違いがなんともわかりにくいので整理する。

公式ドキュメントから

An identifying relationship is one where the child table cannot be uniquely identified without its parent.

これを読んで、最初は、「実線リレーションは1:1の関係しか表せなくないか」と思ってしまった。

続けて、

Typically this occurs where an intermediary table is created to resolve a many-to-many relationship. In such cases, the primary key is usually a composite key made up of the primary keys from the two original tables.

つまり、複合プライマリーキー(以下PK)を使えば、1:nやn:mを実現できる。

点線 実線
画像 kobito.1401760249.084750.png kobito.1401760284.765478.png
子テーブルの外部キーがPKまたは複合PK -
利用例 PeasonとStateをつなぎ、出身地を表す(外部キー≠PK) n:mを表現する中間テーブルなど

要はPKにしたいか否かが決まる。1:nのときに、PKにしたくなる状況が今のところ思いつかなかったので、実線を使うのは、ほとんどn:mのときになるのではないかと思う。

本題

やりたかったことは、MysqlWorkbench作成->DDL自動生成である。

動機

Mysql Workbenchを使う理由

ER図->SQLエクスポートの機能を使うため。 いつも生でテキストエディタで書いていて、効率が悪かったので改善したかった。

外部キー制約を使わない理由

次の3点についてのコストが高いから。

  • INSERT
  • DELETE
  • エラーチェック

INSERTについては、その都度チェックが入ることが容易に想像できるため、外部キー制約を使わないほうがコストが低いとわかるだろう。データが多ければ多いほどそのコストは、単純に考えてもO(log n)~O(n)で増えていくだろう。

DELETEは前述した外部キー制約の際のオプションで制御できるが、ロジックをDB側の設定で追わないといけないのは、保守の面からあまり良くないと考える。

エラーチェックについては、http://iakio.hatenablog.com/entry/2013/04/07/221534 に詳しいが、MySQLが吐くエラーから外部キー制約によるエラーかどうかを判別できないという欠点に起因する。

以上より、外部キーではなくアプリケーション側+トランザクションで整合性を担保する方向で実装したい。

試したこと

記事を探しても出てこなかったので、試行錯誤してみた。

table編集からForeign Keyの削除する。

ER図上でもリレーションが消えてしまうのでうまくいかない。

チェックボックスを外してみる。

これについてもER図上のオーバーレイした時のハイライトがうまく動かず断念。

方法

うまく行った方法というか、SQLエクスポートの時に外部キーを無視するオプションがあったというオチ。これでDDLをさくっと得られる。めっちゃ便利。ただし、意図せぬ設定が間違って入っているかもしれないので、一度確認するとよいと思う。