时间戳

首页 > 解决方案 > Laravel 迁移抛出外键异常

php - Laravel 迁移抛出外键异常

问题描述

我正在尝试使用外部关系编写一个 laravel 数据库迁移。在数据库迁移期间抛出查询异常错误。

我厌倦了使用 laravel 规则迁移表，但在迁移过程中它显示了意外错误。

用户表

    Schema::create('users', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('name', 150);
        $table->string('email', 150)->unique();
        $table->timestamp('email_verified_at')->nullable();
        $table->string('phone', 150);
        $table->unsignedBigInteger('role_id');
        $table->foreign('role_id')->references('id')->on('roles')->onDelete('cascade');
        $table->string('password');
        $table->rememberToken();
        $table->timestamps();
    });

角色表

    Schema::create('roles', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('role_name',255);
        $table->longText('role_description',255);
        $table->integer('sort_order');
        $table->enum('status',['A','I','D'])->comment('A-active','I-inactive,D-delete'); 
        $table->enum('is_deleted',['Y','N'])->comment('Y-yes,N-no');
        $table->timestamps();
        $table->bigInteger('created_by');
        $table->bigInteger('updated_by')->default(0);
        $table->bigInteger('deleted_by')->default(0);
        $table->timestamp('deleted_at')->nullable();
    });

Illuminate\Database\QueryException：SQLSTATE[HY000]：一般错误：1215 无法添加外键约束（SQL：删除级联时更改表jt_users 添加约束users_role_id_foreign外键（role_id）引用jt_roles（ ））id

标签： phplaravelmigration

解决方案

您不能将外键添加到不存在的表中。在您的情况下，您尝试在创建表role_id之前创建一个roles。

在表迁移中，一旦创建roles表，您将需要更新表：usersroles

Schema::create('roles', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->string('role_name',255);
    $table->longText('role_description',255);
    $table->integer('sort_order');
    $table->enum('status',['A','I','D'])->comment('A-active','I-inactive,D-delete'); 
    $table->enum('is_deleted',['Y','N'])->comment('Y-yes,N-no');
    $table->timestamps();
    $table->bigInteger('created_by');
    $table->bigInteger('updated_by')->default(0);
    $table->bigInteger('deleted_by')->default(0);
    $table->timestamp('deleted_at')->nullable();
});

Schema::table('users', function (Blueprint $table) {
    $table->unsignedBigInteger('role_id');
    $table->foreign('role_id')->references('id')->on('roles')->onDelete('cascade');
});

注意：在对表格进行更改时使用Schema::table()而不是。Schema::create()

在迁移的down()方法上roles，您需要删除外键和字段：

Schema::table('users', function (Blueprint $table) {
    $table->dropForeign(['role_id']);
    $table->dropColumn('role_id');
});

推荐阅读