🚮

外部キー制約を受けているテーブルを削除する方法

mocchantai

2024/02/16に公開

こんにちは！mocchantaiです😁

この記事では、Laravelのシーディングプロセス中に外部キー制約を受けているテーブルを安全に削除する方法について解説します。

注意点

動機

データベースのリセットや再シードを行う際、truncateコマンドを用いて特定のテーブルをクリアしようとすると、外部キー制約が存在するためにエラーが発生します。この問題は、例えばUsersテーブルがPostsテーブルを外部キーで参照している場合によく見られます。

エラーメッセージはこのようなものです。
Cannot truncate a table referenced in a foreign key constraint

migrationファイルの作成

状況を再現するために、例としてユーザー(User)と投稿(Post)のテーブルを作成して、どのような場合にエラーが出てしまうのか確認しましょう。
Postのuser_idはUsersテーブルのidに外部キー制約を受けるように設計します。

まず、UsersテーブルとPostsテーブルのmigrationファイルを作成します。

Usersテーブル

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('email')->unique();
    $table->timestamps();
});

Postsテーブル

Schema::create('posts', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->onDelete('cascade');
    $table->string('title');
    $table->text('body');
    $table->timestamps();
});

Seederファイルの作成と実行時のエラーメッセージ

次に、UsersテーブルとPostsテーブルのSeederファイルを作成し、データベースをリフレッシュする際にtruncateを使用します。

UsersTableSeeder

use Illuminate\Support\Facades\DB;

class UsersTableSeeder extends Seeder
{
    public function run()
    {
        DB::table('users')->truncate();

        DB::table('users')->insert([
            'name' => 'John Doe',
            'email' => 'john@example.com',
        ]);
    }
}

PostsTableSeeder

use Illuminate\Support\Facades\DB;

class PostsTableSeeder extends Seeder
{
    public function run()
    {
        DB::table('posts')->truncate();

        DB::table('posts')->insert([
            'user_id' => 1, // 仮に1番目のユーザーが投稿者とする
            'title' => 'サンプル投稿',
            'body' => 'これはサンプルの投稿内容です。',
        ]);
    }
}

Seederを実行した時のエラーメッセージ

php artisan db:seedを実行すると、Usersテーブルをtruncateしようとした際に、PostsテーブルがUsersテーブルを参照しているために次のようなエラーメッセージが表示されます。

SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint (`laravel`.`posts`, CONSTRAINT `posts_user_id_foreign`) (SQL: truncate table `users`)

truncateの解説とエラーメッセージの解説

truncateについて

truncateは、テーブルから全ての行を削除し、テーブルの空間を解放するSQLコマンドです。この操作は非常に高速であり、大量のデータを持つテーブルをリセットする際に有効です。しかし、truncateはテーブルの構造を保持しつつデータだけを削除するため、外部キー制約が存在する場合、データの整合性を維持するために操作がブロックされることがあります。

エラーメッセージの解説

SQLSTATE[42000]は、SQL標準のエラーコードであり、構文エラーやアクセス違反が発生したことを示します。

Cannot truncate a table referenced in a foreign key constraint (`laravel`.`posts`, CONSTRAINT `posts_user_id_foreign`) (SQL: truncate table `users`)は、truncate操作が外部キー制約によって参照されているテーブルで実行されようとしたときに生じるエラーです。つまり、truncateを使用してUsersテーブルをリセットしようとすると、PostsテーブルがUsersテーブルを参照しているために操作がブロックされます。

解決方法

方法１：外部キーのチェックを無効にする

外部キーのチェックを無効にしてからtruncateを使い、その後で外部キーのチェックを再度有効にします。この方法は、テーブルの自動インクリメントの値もリセットされます。

UsersTableSeeder.php

use Illuminate\Support\Facades\DB;

class UsersTableSeeder extends Seeder
{
    public function run()
    {
+       DB::statement('SET FOREIGN_KEY_CHECKS=0;');//外部キーのチェックを無効にする
        DB::table('users')->truncate();
+       DB::statement('SET FOREIGN_KEY_CHECKS=1;');//外部キーのチェックを有効にする

        DB::table('users')->insert([
            'name' => 'John Doe',
            'email' => 'john@example.com',
        ]);
    }
}

PostsTableSeeder.php

use Illuminate\Support\Facades\DB;

class PostsTableSeeder extends Seeder
{
    public function run()
    {
+       DB::statement('SET FOREIGN_KEY_CHECKS=0;');//外部キーのチェックを無効にする
        DB::table('posts')->truncate();
+       DB::statement('SET FOREIGN_KEY_CHECKS=1;');//外部キーのチェックを有効にする

        DB::table('posts')->insert([
            'user_id' => 1, // 仮に1番目のユーザーが投稿者とする
            'title' => 'サンプル投稿',
            'body' => 'これはサンプルの投稿内容です。',
        ]);
    }
}

方法２：`delete`操作を使う

deleteメソッドを使用してテーブルから全レコードを削除します。この方法では、テーブルの自動インクリメントの値はリセットされませんが、外部キー制約によるエラーを避けることができます。

UsersTableSeeder.php

use Illuminate\Support\Facades\DB;

class UsersTableSeeder extends Seeder
{
    public function run()
    {
-       DB::table('users')->truncate();
+       DB::table('users')->delete();//truncateの代わりにdeleteを使用する

        DB::table('users')->insert([
            'name' => 'John Doe',
            'email' => 'john@example.com',
        ]);
    }
}

PostsTableSeeder.php

use Illuminate\Support\Facades\DB;

class PostsTableSeeder extends Seeder
{
    public function run()
    {
-       DB::table('posts')->truncate();
+       DB::table('posts')->delete();//truncateの代わりにdeleteを使用する

        DB::table('posts')->insert([
            'user_id' => 1, // 仮に1番目のユーザーが投稿者とする
            'title' => 'サンプル投稿',
            'body' => 'これはサンプルの投稿内容です。',
        ]);
    }
}