なぜ作ったのか

外部キー制約は言うまでもなくデータの不整合を避けるのに有用ですが、いくつかの理由からこれを用いない選択肢も存在すると思われます。この辺りについては以下の記事なども参考にすると良さそうです。

外部キー制約を設けないことによるデメリットも幾つか生じると思っていて、その一つで自分が気になっている点として、「ER 図をツールで自動生成しにくい」があります。例えば MySQL Workbench は sql ファイルや稼働する MySQL データベースから ER 図を自動生成してくれる機能を持ちますが、エンティティ間のリレーションは外部キー制約を無くすと追えなくなります。この問題に対する緩和策として SchemaSpy を用いれば主キーをベースに外部キー制約を推測して ER 図生成を行ってくれます。しかしながらこの推測のルールはあまり小回りが効くものではありません。

SchemaSpy のやっているような制約の推測を、図の自動生成とは分離してシンプルに扱い、かつルールを細かく追加したり ON/OFF したりしたい。そう考え今回紹介するツールの実装を開始しました。

実装など

ワンバイナリでサクッと動かしたい希望があったため Go で書いています。 MySQL データベースからテーブル定義や主キーの情報を集めるため information_schema データベースを参照し、推測を行って制約追加クエリを標準出力に出します。現在サポートする推測ルールは以下の通りです。

あるカラム名と同じ名前の、別のテーブルのカラムが存在し、かつそれが id という名前ではなくそのテーブルで主キーである場合外部キー制約を追加する
あるカラム名が <別のテーブル名の単数形>_id であり、かつ別のテーブルで id が主キーである場合外部キー制約を追加する

標準出力に最終的に吐き出すフォーマットとしては今後は必要であれば PlantUML をサポートしたりしてもいいかも、と考えております。

動作例

以下のような、外部キー制約が全く設けられていないテーブル定義があったとします。

CREATE TABLE IF NOT EXISTS users (
  id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name  VARCHAR(255) NOT NULL,
  age   INT UNSIGNED NOT NULL,
  email VARCHAR(255) NOT NULL,
  PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS contents (
  id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  text  TEXT NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS content_comments (
  content_comment_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  text               TEXT NOT NULL,
  content_id         INT UNSIGNED NOT NULL,
  user_id            INT UNSIGNED NOT NULL,
  PRIMARY KEY(content_comment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS content_comment_reactions (
  content_comment_id INT UNSIGNED NOT NULL,
  user_id            INT UNSIGNED NOT NULL,
  reaction_id        INT UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS reactions (
  id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  image_url VARCHAR(255) NOT NULL,
  PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

これをベースに MySQL Workbench で ER 図を生成すると以下のようになります。

f:id:syu_cream:20180506200831p:plain

これに対し hakagi はいくつか外部キー制約を追加するクエリを吐き出してくれます。

$ ./hakagi -dbuser root -targets hakagi_example
ALTER TABLE content_comment_reactions ADD CONSTRAINT FOREIGN KEY (content_comment_id) REFERENCES content_comments(content_comment_id);
ALTER TABLE content_comment_reactions ADD CONSTRAINT FOREIGN KEY (user_id) REFERENCES users(id);
ALTER TABLE content_comment_reactions ADD CONSTRAINT FOREIGN KEY (reaction_id) REFERENCES reactions(id);
ALTER TABLE content_comments ADD CONSTRAINT FOREIGN KEY (content_id) REFERENCES contents(id);
ALTER TABLE content_comments ADD CONSTRAINT FOREIGN KEY (user_id) REFERENCES users(id);
ALTER TABLE contents ADD CONSTRAINT FOREIGN KEY (user_id) REFERENCES users(id);

このクエリも用いることで、以下のような ER 図を生成することができました。

f:id:syu_cream:20180506201000p:plain