Laravel 8.54迁移与填充报错：无法截断带外键约束的表

SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint (hospitalmanagement.role_user, CONSTRAINT role_user_user_id_foreign FOREIGN KEY (user_id) REFERENCES hospitalmanagement.users (id)) (SQL: truncate table users)

数据库迁移代码 #

users表迁移 #

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->text('photo')->default('images/default-user-photo.jpg');
        $table->string('email')->unique();
        $table->integer('phone')->unique();
        $table->integer('status')->default(1);
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password');
        $table->string('slug')->nullable();
        $table->rememberToken();
        $table->timestamps();
    });
}

roles表迁移 #

public function up()
{
    Schema::create('roles', function (Blueprint $table) {
        $table->id();
        $table->string('name')->unique();
        $table->string('slug')->unique();
        $table->timestamps();
    });
}

role_user关联表迁移 #

public function up()
{
    Schema::create('role_user', function (Blueprint $table) {
        $table->primary(['role_id', 'user_id']);
        $table->foreignId('role_id')->constrained()->onDelete('cascade');
        $table->foreignId('user_id')->constrained()->onDelete('cascade');
        $table->timestamps();
    });
}

Seeder代码 #

RoleSeeder代码 #

public function run()
{
    // Role::truncate();

    $role = new Role();
    $role->name = 'Super Admin';
    $role->slug = 'super-admin';

    $role = new Role();
    $role->name = 'Admin';
    $role->slug = 'admin';

    $role = new Role();
    $role->name = 'Doctor';
    $role->slug = 'doctor';
}

UserSeeder代码 #

public function run()
{
    User::truncate();

    $user = new User();
    $user->name = 'Super Admin';
    $user->email = 'admin@gmail.com';
    $user->phone = '0278596525';
    $user->role_id = 1;
    $user->password = '$2y$10$wSoyqSUQZGC8MeEcbqu2gugCgGTOuJz5AiKCphM6W2rK8swfb2/ky';
    $user->created_at = Carbon::now()->toDateTimeString();
    $user->save();

    $user = new User();
    $user->name = 'Admin';
    $user->email = 'add@gmail.com';
    $user->phone = '0247859662';
    $user->role_id = 2;
    $user->password = '$2y$10$wSoyqSUQZGC8MeEcbqu2gugCgGTOuJz5AiKCphM6W2rK8swfb2/ky';
    $user->created_at = Carbon::now()->toDateTimeString();
    $user->save();

    $user = new User();
    $user->name = 'Doctor';
    $user->email = 'doc@gmail.com';
    $user->phone = '0247899663';
    $user->role_id = 3;
    $user->password = '$2a$12$CMMLYGNwNreJlghJY3O3b.U4k2QJ7fhXdAA4XtfRXTPEEtBZenYNq';
    $user->created_at = Carbon::now()->toDateTimeString();
    $user->save();
}

解决步骤 #

1. 修复Truncate的外键约束问题 #

users表被role_user关联，直接执行User::truncate()会触发外键约束错误，可选三种处理方式：

方式一：先清空关联表

在UserSeeder开头先清空关联表，再操作主表：

DB::table('role_user')->truncate();
User::truncate();
Role::truncate(); // 如需重置角色表可添加

方式二：临时关闭外键检查

执行Truncate前后临时关闭并恢复外键约束：

DB::statement('SET FOREIGN_KEY_CHECKS=0;');
User::truncate();
DB::statement('SET FOREIGN_KEY_CHECKS=1;');

方式三：用delete代替truncate

delete()会删除所有记录，但不会重置自增ID，适合不需要重置ID的场景：

User::delete();

2. 修复Seeder中的逻辑错误 #

（1）RoleSeeder未保存角色

当前代码仅创建了Role对象但未写入数据库，修改后：

public function run()
{
    Role::truncate();

    Role::create([
        'name' => 'Super Admin',
        'slug' => 'super-admin'
    ]);

    Role::create([
        'name' => 'Admin',
        'slug' => 'admin'
    ]);

    Role::create([
        'name' => 'Doctor',
        'slug' => 'doctor'
    ]);
}

（2）UserSeeder错误使用role_id字段

users表无role_id字段，多对多关联需通过role_user表绑定，修改UserSeeder：

public function run()
{
    DB::table('role_user')->truncate();
    User::truncate();

    // 创建超级管理员并关联角色
    $superAdmin = User::create([
        'name' => 'Super Admin',
        'email' => 'admin@gmail.com',
        'phone' => '0278596525',
        'password' => '$2y$10$wSoyqSUQZGC8MeEcbqu2gugCgGTOuJz5AiKCphM6W2rK8swfb2/ky',
        'created_at' => Carbon::now()->toDateTimeString()
    ]);
    $superAdmin->roles()->attach(1);

    // 创建管理员并关联角色
    $admin = User::create([
        'name' => 'Admin',
        'email' => 'add@gmail.com',
        'phone' => '0247859662',
        'password' => '$2y$10$wSoyqSUQZGC8MeEcbqu2gugCgGTOuJz5AiKCphM6W2rK8swfb2/ky',
        'created_at' => Carbon::now()->toDateTimeString()
    ]);
    $admin->roles()->attach(2);

    // 创建医生并关联角色
    $doctor = User::create([
        'name' => 'Doctor',
        'email' => 'doc@gmail.com',
        'phone' => '0247899663',
        'password' => '$2a$12$CMMLYGNwNreJlghJY3O3b.U4k2QJ7fhXdAA4XtfRXTPEEtBZenYNq',
        'created_at' => Carbon::now()->toDateTimeString()
    ]);
    $doctor->roles()->attach(3);
}

（3）确保模型定义多对多关联

在User模型中添加：

public function roles()
{
    return $this->belongsToMany(Role::class);
}

在Role模型中添加：

public function users()
{
    return $this->belongsToMany(User::class);
}

内容的提问来源于stack exchange，提问作者Lucy