Laravel migration throw database query exception error

Question

I'm trying to write a laravel database migration with foreign relation. during database migration throwing query exception error.

I tired to migrate tables using laravel rules but during migration it showing the unexpected error.

Users Table

    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();
    });

Roles Table

    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]: General error: 1215 Cannot add foreign key constraint (SQL: alter table jt_users add constraint users_role_id_foreign foreign key (role_id) references jt_roles (id) on delete cascade)

It seems you missed to define the column definition for the role_id in users migration. Also the migration oder should matter... you must ensure the roles migration should run first. — Vikash Pathak

thisiskelvin thisiskelvin · Accepted Answer · 2019-06-07T09:30:17

You can not add a foreign key to a table which doesn't exist. In your case, you are trying to create a role_id before the roles table is created.

Within your roles table migration, you will need to update the users table once the roles table has been created:

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');
});

Note: Use Schema::table() instead of Schema::create() when making changes to a table.

On the down() method of the roles migration, you need to drop the foreign key and field:

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

Laravel migration throw database query exception error

2 Answers