第13回　MySQL Workbenchを使ってER図を編集する

前々回（第11回 MySQL Workbenchを使って既存のデータベースからER図を作成する）では、現在DBにある情報からER図を作成しする方法を紹介しました。しかし、第11回でも少し説明した通り、ER図を出力した際にテーブル間の関係を自動的に定義できるのは外部キーが指定されている場合だけです。

近年採用されているWebアプリケーションフレームワークの一部では、外部キーを定義せずアプリケーションの内部でテーブル間の関係を定義するものもあり、外部キーを適切に定義していないこともあると思います。

今回はそういった外部キーが設定されていない場合に、MySQL Workbenchを使ってER図上のテーブル間の関係を定義する方法をお伝えしていきたいと思います。

デモンストレーション環境について

今回は第11回で使用したDocker上のMySQLを利用して、新しいテーブルを定義していきます。

$ cat test2.sql
CREATE DATABASE `blog2`;

USE `blog2`;

CREATE TABLE `users`(
        `user_id` int,
        `e_mail` text,
   `password` text,
   PRIMARY KEY(`user_id`)
)engine=InnoDB;

CREATE TABLE `blogs`(
        `user_id` int,
    `blog_id` int,
    `body` text
)engine=InnoDB;

第11回で定義したSQLとの違いを見るためにdiffを取ってみました。diffを行った結果は以下のとおりになります。

$ diff test.sql test2.sql
1c1
< CREATE DATABASE `blog`;
---
> CREATE DATABASE `blog2`;
3c3
< USE `blog`;
---
> USE `blog2`;
15,18c15
<     `body` text,
<     CONSTRAINT `fk_user_id`
<     FOREIGN KEY (`user_id`)
<     REFERENCES `users`(`user_id`)
---
>     `body` text

簡単に差分について説明しますと、データベース名がblogからblog2になっている、usersとblogsのテーブル間に外部キーが宣言されていないという違いがあります。

第11回の手順と同様にSQLを実行して、MySQL Workbenchで上記で作成したblog2のER図を出力した結果が図1になります。

今回作成したテーブルのER図との比較のため、第11回で作成したER図を図2として掲載します。

前回の外部キーが指定されているテーブルと違い、テーブル間の関係性がER図上でも定義されていないことがわかると思います。

今回は、新しく作成したblog2のER図（図1）に対して、テーブル間の関係を定義したり、新しくテーブルを追加していきたいと思います。

ER図を書くための操作説明

ここではまずはじめにMySQL WorkbenchでER図を書く際に使う画面の説明を行っていきます。説明はMac版で行いますが、Windows、Linux共にほぼ同じ動作で行えます。

おおまかに説明すると、左側のウィンドウにはモデリングがどのように行われているかが表示されていて、右側のウィンドウにはテーブルを定義する際に便利なテンプレートが定義されています。そして今回ER図を書くためのエディタが中央に表示されています。

右側と左側のウィンドウに関しては右上の検索バーの隣にあるボタンで表示/非表示を切り替えることができます。今回はどちらも利用しないので図4のように閉じた状態にしておきます。

次に、左側に表示されているER図を書くためのメニューの要素について上から順に説明を行っていきます。縦に一気に表示してしまうと画像が長くなってしまうため、4つに区切り説明を行っていきます。

基本操作

ここではER図を見たり、テーブルやビューをわかりやすく配置したり、削除するために使用するメニューが配置されています。

1番上のマウスカーソルを選択しているときは、ER図内のテーブルやビューや関係などを選択して移動させたり、詳細を確認することができます。試しにblog2データベース内のblogテーブルをダブルクリックしてみると、図6のようなタブが開きます。

こちらのタブからは既存のテーブル内にあるカラムやインデックスを追加や削除などのテーブル構造の操作だけでなく、Insertsからデータの追加や変更、削除ができるなど、テーブルの操作に必要なことが一通り行うことができるようになっています。今回は詳細については省略します。

2番目の手のマークをドラッグをすることで、ER図内部を移動することができるようになります。また、こちらを使用していてもテーブルやリレーションをクリックすると、テーブルの詳細画面を見ることができます。

3番目の消しゴムのマークはER図上に表示されているオブジェクトを削除するときに使います。このボタンを選択した状態でオブジェクトをクリックすると、そのオブジェクトが削除されます。

ER図を見やすくする機能

ここでは、ER図を見やすくするために使うことができる幾つかの機能を使うことができます。上から順に説明を行っていきます。

1番目はレイヤーの指定です。図8のように背景を指定することができます。ER図上でグループなどを表現したい場合に使用することができます。

2番目はテキストの追加です。ER図に対して何か画像での説明を書き加えたい場合に、ボタンを選択しER図をクリックすると、図9のようにテキストを追加することができます。

3番目は画像の追加です。何か説明用の画像を加えたい場合など、写真を簡単に追加することができます。

テーブル・ビュー・ルーチンの追加

図10　ER図のサイドメニュー（3） — **図10**　ER図のサイドメニュー（3）

上から順にテーブル、ビュー、ルーチンといったMySQLのオブジェクトを作成することができます。

テーブルを例に取って説明を行いますが、図11のようにカラムとインデックスが存在しない空のテーブルが作成されます。作成されたER図上のテーブルをダブルクリックすると、テーブルの詳細についてのウィンドウが以下のように開くので、そこでテーブルの操作を行うことができます。

関係の操作

ここには、テーブル間の関係を操作するための操作をするための機能があります。

図12　ER図のサイドメニュー（4） — **図12**　ER図のサイドメニュー（4）

注意することは、上から5つは新しくカラムを追加して関係を作成する点です。最後のスポイトのマークが表示されているボタンは、今テーブルに存在しているカラムから関係を定義することができます。

ここでは説明を行いませんが、データの関係が依存・非依存関係にあることや1対1・1対n・多対多の関係を定義することができます。

テーブル間の関係を定義する

本題に戻り、blogとusersの関係を定義してみたいと思います。usersテーブルの要素が1に対してblogテーブルの要素がnになるような関係を定義していきます。

今回のblogテーブルでは、すでにuser_idが存在しています。そのため、関係の操作の一番最後にあったテーブル内にすでに存在しているカラムをキーにするボタンを選択して、関係を紐付ける操作を行います。ボタンを選択した後でblogのuser_idをクリックします。すると以下のようにblogのuser_idが選択された状態になります。

図13　blogのuser_idをクリック — **図13**　blogのuser_idをクリック

次にblogのuser_idに紐付けるusersのuser_idをクリックします。すると図14のように関係が定義されます。

こちらの関係をダブルクリックしてみると、関係の詳細に関してテーブルの時と同様に関係の詳細タブが1対nを1対1に変更することができます。

順番を逆にすると関係が逆についてしまうのでご注意ください。

関係の削除の方法について

間違えてテーブル間に付けてしまった場合や、アプリケーションの都合によって、テーブル間の関係を削除する必要があるかと思います。そういった場合のために関係の削除について説明します。

削除したい関係にマウスカーソルを合わせて右クリックをすると、図16のようなメニューが表示されます。

メニュー中の「Delete」を選択肢すると図17のようなダイアログがポップアップします。

ここで表示されている内容は、外部キーとして定義していたキーを削除するか（DELETE⁠）⁠、それとも残すか（KEEP）といった内容になります。それぞれ違う結果になるので、DELETEを行った方から確認していきます。図18のようにblogsテーブル内にあったuser_idが削除されています。

次にKeepを行った場合を確認します。Keepを行った場合は、図19のようにblogsテーブル内にuser_idが削除されずに残っていることがわかるかと思います。

もし何らかの操作に失敗した場合はEditメニューから取り消し（Undo）ができるので、失敗を恐れずにいろいろと試してみてください。

データベースに反映してみる

MySQL Workbenchでは、ER図で定義した内容をデータベースに対して以下のように反映することができます。Databaseメニューの中にある「Forward Engineer ...」を選択します。

次に反映したいデータベースがあるサーバを選択します。選択ができたらContinueをクリックします。

続いて反映する際の設定が選択できるようになります。一番上に表示されている「Skip creation of FORREIGN KEYS」を使用すると、外部キーの反映だけ除くことができます。外部キーとして宣言できないような状態になっていたとしても、このオプションにチェックを入れると、ER図には関係を残しつつデータベースには反映させないといったことができます。必要に応じて選択ができたらContinueをクリックします。

次に、実行するテーブルやViewなどを選択することができるようになります。今回はテーブルに対しての反映なので、そのままContinueをクリックします。最後に実行されるSQLの内容が図23のように表示されるので、ここで最終確認を行います。Cotinueをクリックすると、SQLが実行されデータベースに内容が反映されます。

まとめ

今回と第11回を通して、MySQL WorkbenchでのER図の出力や書き方についてお伝えしました。

ER図は開発の初期にデータベースが無い場合などにも、データ構造のイメージを他の開発者に伝えるために有用な情報となります。また、MySQL Workbenchで作成したER図はデータベースに適用することも可能なので、開発を始める時に、どのようなテーブル構造にするか決める際などにも利用してみてはいかがでしょうか。