[Laravel] migrationでMySQLに対して外部キー制約を設定する方法

Laravel
2021.12.29

はじめに

記事を見ていただいて、ありがとうございます。

Webエンジニアをしているsannoと申します。

アプリケーションを開発する上でDBは大切な存在ですよねー

昨今ではNoSQLが使われることも増えていると思いますが、アプリケーションの特性に合わせてRDBMSを選択することも普通にあると思います。

今回の記事ではRDBMSの特徴を表す、外部キー制約について解説したいと思います。

RDBMSのACID特性のConsistency(一貫性/整合性)に関連するものですね。

小難しい感じで入ってしまいましたが、LaravelのmigrationでMySQLに対して外部キー制約を設定する方法を解説しますので、興味をもっていただけましたら見ていってください。

それでは外部キー制約の設定方法について、見てゆきましょう!

もし、この記事で解説している内容に誤りがございましたら、コメント等でご指摘いただけると幸いです。

環境

$ php --version
PHP 8.0.2 (cli) (built: Feb  9 2021 19:20:59) ( NTS )
Copyright (c) The PHP Group
Zend Engine v4.0.2, Copyright (c) Zend Technologies

$ php artisan --version
Laravel Framework 8.76.2
$ composer info laravel/tinker
name     : laravel/tinker
descrip. : Powerful REPL for the Laravel framework.
keywords : REPL, Tinker, laravel, psysh
versions : * v2.6.3

$ mysql --version
mysql  Ver 8.0.27 for Linux on x86_64 (MySQL Community Server - GPL)

テーブル構成を考えるための業務要件

この記事でテーブル構成を考えるのは何かのECサイトを想定してみます。

重要な業務要件は以下の2点です。

  1. このECサイトでは会員が退会したら会員情報は物理削除して購入履歴等の情報も残さないことを会員登録時の規約とする
  2. 商品はカテゴリーに分類されていて必ずいずれかのカテゴリーに属する

ECサイトの会員が何か商品を購入して購入履歴が記録されるイメージです。

この業務要件からテーブル構成は以下のもので考えてゆきたいと思います。

ER purchase histories
ER purchase histories
ER purchase histories

本当のECサイトのテーブルはこんな単純な構成ではないと思いますし、ECサイトの運営側としては退会した会員の購入履歴の情報も、顧客分析などで使うために残しておきたいと思うかもしれませんが、今回は解説のためにシンプルな構成にてご容赦ください。

外部キー制約と設定手順

migration

それぞれのmigrationファイルを作ってみましょう。

まずはmembersテーブルのmigrationを作ります。

$ php artisan make:migration create_members_table

migrationファイルの中身は以下のような感じです。

<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateMembersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('members', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->timestamps();
        });
    }

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

次はgoods_categoriesテーブルのmigrationを作ります。

$ php artisan make:migration create_goods_categories_table

migrationファイルの中身は以下のような感じです。

<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateGoodsCategoriesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('goods_categories', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->timestamps();
        });
    }

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

最後にpurchase_historiesテーブルのmigrationを作ります。

$ php artisan make:migration create_purchase_histories_table

migrationファイルの中身は以下のような感じです。

<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreatePurchaseHistoriesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('purchase_histories', function (Blueprint $table) {
            $table->id();
            $table->foreignId('member_id')->constrained()->onDelete('CASCADE');
            $table->foreignId('goods_category_id')->constrained()->onDelete('RESTRICT');
            $table->string('goods');
            $table->timestamps();
        });
    }

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

purchase_historiesのmigrationが一番のポイントだと思います。

member_idとgoods_category_idに設定している外部キー制約の部分ですね。

ここで、今回の業務要件を思い出してみましょう。

  1. このECサイトでは会員が退会したら会員情報は物理削除して購入履歴等の情報も残さないことを会員登録時の規約とする
  2. 商品はカテゴリーに分類されていて必ずいずれかのカテゴリーに属する

1つ目の業務要件は以下のCASCADEで表現しています。

$table->foreignId('member_id')->constrained()->onDelete('CASCADE');

あるmemberのレコードが削除されると、それに紐づくpurchase_historiesも外部キー制約で削除されるということです。

会員の退会(削除)と同時にそれに紐づく購入履歴も必ず削除されていないと、規約違反になってしまうので、それを外部キー制約で縛っておくということですね。

2つ目の業務要件は以下のRESTRICT設定で表現しています。

$table->foreignId('goods_category_id')->constrained()->onDelete('RESTRICT');

あるgoods_categoriesのレコードに紐づくpurchase_historiesが存在している限りは、そのgoods_categoriesのレコードは削除されないということです。

あるカテゴリーに属する商品の購入履歴が存在する場合に、そのカテゴリーを削除してしまうといずれのカテゴリーにも属さない商品の購入履歴が残ってしまうので、そうならないように外部キー制約で縛っておくということですね。

今回は設定しませんでしたが、外部キー制約には他にSET NULL, NO ACTION, SET DEFAULTの設定もあります。

外部キー制約の、より詳細な解説は公式等の記事を参照していただくと良いかと思います。

model

テーブル同士のリレーションをmodelで設定しておきます。

必要なmodelをまずは作ってしまいましょう。

$ php artisan make:model Member
$ php artisan make:model GoodsCategory
$ php artisan make:model PurchaseHistory

それぞれのmodelの中身は以下のようにします。

1対多の関係でリレーションを設定します。

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Member extends Model
{
    protected $table = 'members';
    protected $fillable = [
        'name',
        'created_at',
        'updated_at',
    ];
    public function purchaseHistories()
    {
        return $this->hasMany(PurchaseHistory::class);
    }
}
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class GoodsCategory extends Model
{
    protected $table = 'goods_categories';
    protected $fillable = [
        'name',
        'created_at',
        'updated_at',
    ];
    public function purchaseHistories()
    {
        return $this->hasMany(PurchaseHistory::class);
    }
}
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class PurchaseHistory extends Model
{
    protected $table = 'purchase_histories';
    protected $fillable = [
        'member_id',
        'goods_category_id',
        'goods',
        'created_at',
        'updated_at',
    ];
    public function member()
    {
        return $this->belongsTo(Member::class);
    }
    public function goodsCategory()
    {
        return $this->belongsTo(GoodsCategory::class);
    }
}

seeder

外部キー制約の動作を確認するために、seederでデータも入れておきましょう。

こちらも必要なseederをまずは作ってしまいましょう。

$ php artisan make:seeder MemberSeeder
$ php artisan make:seeder GoodsCategorySeeder
$ php artisan make:seeder PurchaseHistorySeeder

それぞれのseederの中身は以下のように設定します。

<?php
namespace Database\Seeders;
use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;
use DateTime;
class MemberSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $now = new DateTime();
        DB::table('members')->insert([
            [
                'name' => 'Bob',
                'created_at' => $now,
                'updated_at' => $now
            ],
            [
                'name' => 'Tom',
                'created_at' => $now,
                'updated_at' => $now
            ],
            [
                'name' => 'Alice',
                'created_at' => $now,
                'updated_at' => $now
            ],
        ]);
    }
}
<?php
namespace Database\Seeders;
use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;
use DateTime;
class GoodsCategorySeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $now = new DateTime();
        DB::table('goods_categories')->insert([
            [
                'name' => '食料品',
                'created_at' => $now,
                'updated_at' => $now,
            ],
            [
                'name' => '飲料',
                'created_at' => $now,
                'updated_at' => $now,
            ],
            [
                'name' => '家電',
                'created_at' => $now,
                'updated_at' => $now,
            ],
            [
                'name' => '家具',
                'created_at' => $now,
                'updated_at' => $now,
            ],
            [
                'name' => '衣料品',
                'created_at' => $now,
                'updated_at' => $now,
            ],
            [
                'name' => '日用品',
                'created_at' => $now,
                'updated_at' => $now,
            ],
            [
                'name' => '健康食品',
                'created_at' => $now,
                'updated_at' => $now,
            ],
        ]);
    }
}
<?php
namespace Database\Seeders;
use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;
use DateTime;
class PurchaseHistorySeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $now = new DateTime();
        DB::table('purchase_histories')->insert([
            [
                'member_id' => 1,
                'goods_category_id' => 4,
                'goods' => 'ワーキングデスク',
                'created_at' => $now,
                'updated_at' => $now,
            ],
            [
                'member_id' => 1,
                'goods_category_id' => 4,
                'goods' => 'ワーキングチェア',
                'created_at' => $now,
                'updated_at' => $now,
            ],
            [
                'member_id' => 1,
                'goods_category_id' => 7,
                'goods' => 'プロテイン',
                'created_at' => $now,
                'updated_at' => $now,
            ],
            [
                'member_id' => 1,
                'goods_category_id' => 7,
                'goods' => 'マルチビタミンサプリメント',
                'created_at' => $now,
                'updated_at' => $now,
            ],
            [
                'member_id' => 2,
                'goods_category_id' => 2,
                'goods' => '炭酸水24本1ケース',
                'created_at' => $now,
                'updated_at' => $now,
            ],
            [
                'member_id' => 2,
                'goods_category_id' => 5,
                'goods' => 'コート',
                'created_at' => $now,
                'updated_at' => $now,
            ],
            [
                'member_id' => 2,
                'goods_category_id' => 5,
                'goods' => 'マフラー',
                'created_at' => $now,
                'updated_at' => $now,
            ],
            [
                'member_id' => 3,
                'goods_category_id' => 1,
                'goods' => 'チョコレート詰め合わせ',
                'created_at' => $now,
                'updated_at' => $now,
            ],
            [
                'member_id' => 3,
                'goods_category_id' => 1,
                'goods' => 'アイスアソートパック',
                'created_at' => $now,
                'updated_at' => $now,
            ],
            [
                'member_id' => 3,
                'goods_category_id' => 3,
                'goods' => '美顔器',
                'created_at' => $now,
                'updated_at' => $now,
            ],
        ]);
    }
}

個別に作成したseederは以下のようにまとめて呼び出します。

<?php
use Illuminate\Database\Seeder;
use Database\Seeders\MemberSeeder;
use Database\Seeders\GoodsCategorySeeder;
use Database\Seeders\PurchaseHistorySeeder;
class DatabaseSeeder extends Seeder
{
    /**
     * Seed the application's database.
     *
     * @return void
     */
    public function run()
    {
        $this->call([
            MemberSeeder::class,
            GoodsCategorySeeder::class,
            PurchaseHistorySeeder::class,
        ]);
    }
}

seederを実行しましょう。

$ php artisan db:seed

制約の確認

テーブルとレコードを用意できたので、本当に制約が設定されているか確認してみましょう。

レコード操作はtinkerを使って操作してみたいと思います。

>>> Member::find(1)->purchaseHistories()->count();
=> 4

これはBobさんの購入履歴件数ですね。

seederで4件登録したので妥当でしょう。

>>> Member::destroy(1)
=> 1

>>> PurchaseHistory::where('member_id', 1)->count()
=> 0

membersテーブルからBobさんのレコードを削除すると、purchase_historiesテーブルからもBobさんの購入履歴が削除されました。

CASCADEの設定は問題なさそうです。

>>> GoodsCategory::find(1)->purchaseHistories()->count()
=> 2

これは食料品に属する購入履歴の件数ですね。

seederで2件登録したので妥当でしょう。

>>> GoodsCategory::destroy(1)
Illuminate\Database\QueryException with message 'SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`any_database`.`purchase_histories`, CONSTRAINT `purchase_histories_goods_category_id_foreign` FOREIGN KEY (`goods_category_id`) REFERENCES `goods_categories` (`id`) ON DELETE RESTRICT) (SQL: delete from `goods_categories` where `id` = 1)'

外部キー制約のエラーが発生しています。

goods_categoriesテーブルの食料品に属するpurchase_historiesテーブルの購入履歴が存在するので、削除できないということですね。

RESTRICTの設定も問題なさそうです。

おわりに

あるアプリケーションのデータ設計をした人だけが、そのアプリケーションの保守を行い続けることは稀だと思います。

そんな時にアプリケーションの動作でしかデータの持ち方の制約を制御していないと、どこかの時点で制約を破るような機能が実装されてしまうかもしれません。

ですので、何かデータの持ち方に制約があるのであれば、RDBMSの仕組みを使って縛りをかけておくのは有効だと思います。

制約を破るような不整合データの存在をRDBMSの仕組みで自動的に補正したり、エラーによって許可しないことでConsistency(一貫性/整合性)が保たれます。

ぜひ、皆さんも外部キー制約を活用してみてください。

ここまで記事を読んでいただいて、ありがとうございました!

コメント

タイトルとURLをコピーしました