SQLアンチパターン~第4章~ - hironekosunのブログ

趣旨

最近学習をサボっておりアウトプットも行っていない。エンジニアとして後退する老害になりかねない。こんなんじゃだめだ！という理由で書きます。
最近「ん？ちょっと何言っているわからない」ってことが本業外であり、会話を成立させるには、体系的に理解しているのでなくちゃんと言語化できるレベルで理解しようねってことでまとめます。

おおまかな流れ

みんな大好き「SQLアンチパターン」の第4章キーレスエントリー(外部キー嫌い)を再読とまとめ
- 物量的に全然多くなく主張も簡潔です。
なぜ第4章なのかの話
Laravel で外部キー制約を設定する
まとめ

第4章の主張

パフォーマンスが改善し、単純複雑を問わず、データの変更における参照整合性の意地に役立つよってデーターベースでのミスを未然に防ぐために外部キー制約を用いましょう。

主張に関して

基本的に例外を除いて外部キー制約を使わない理由は、あまりないですよって話。

アンチパターン

そもそも外部キー制約を適用できない
DB の設計を柔軟にしなければならない

アンチパターンの見つけ方

A テーブルには存在するけど、B のテーブには存在しない値を調べるクエリはどう書く？
A テーブルの値が、別のテーブルへの挿入に使われている子を簡単にチェックする方法はある？
外部キーは、DB の実行速度を遅くするからつかってはならない
- 外部キー制約を設けることでの起きる問題より設けない時の問題の方が障害になる可能性が高い

以上の発言があるとアンチパターンの兆候がある

なぜ第4章の話なのか

データ構造を追いかける際に(ER図吐き出したり)外部キーがあると大変便利でだよね。(だから使ってくださいと言いたい)
なので職場に外部キー設定しません！っていう人がいたら第4章をお経のように読み上げ続けましょう。個人的に外部キーがないデータの全体構造を理解するのは、できるとはおもいますが時間の浪費になるので勿体ないことになるんだろうな〜っておもっています。仕事上時間の浪費は、本当に無駄です。

Laravelで外部キー制約を設定する

前提：MySQL, MariaDB
migration fileを作成し、fileを編集します。

php artisan make:migration CreatePosts
php artisan make:migration CreateComments

今回は、外部キーを設定するのが目的なので最小限のカラムを書きます(型は気にせず)

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreatePosts extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('user_id')->comment('user_idを格納します');
            $table->text('content')->comment('投稿内容を保存します');
            $table->timestamps();

            $table->foreign('user_id')
                ->references('id')
                ->on('users');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('posts');
    }
}

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateComments extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('comments', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('post_id')->comment('posts tableのidを格納します。');
            $table->text('comment')->comment('コメントを格納します');
            $table->timestamps();

            $table->foreign('post_id')
                ->references('id')
                ->on('posts');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('comments');
    }
}

php artisan migrate

注意点としては、外部キーを設定する対象の型を合わせないとならない点です
- 最新のLaravelだとmigration fileを作成した際にデフォルトで記載ある、auto incrementsのカラムがidとなった点なりました。こちらの型(Laravel上で使用するメソッド)は、unsignedBigIntegerとなるので注意してください。
- また外部キー制約を設定する際にindexも作成してくれているんですが、table名とid名が長いとエラーになりますので、foreign()の第2引数にindex名を指定することで回避が可能です。
- 個別(create時ではなくaddなどで)に外部キーを設定した際は、必ずdownを記述しましょう。エラーでmigrateがエラーになります。
外部キーの設定を行う際に、onDelete()という親tableへの動作に応じて振る舞いを連動させるメソッドがあるんですが、今回は、設定しません。
ちなみに外部キー設定などに関する情報は、information_schemaというDBに保存されます。

まとめ

外部キー制約は、ちゃんと設定しましょう
Laravelで普通に設定する分には、なんら問題なく動作することがわかりました。しっかりFWの使える機能は、利用しましょう