How to fix "Foreign key constraint is incorrectly formed" in SQL query in PHP app

Question

Issue

I'm making a login app with laravel , i want to make the role for users but i came accross this error

Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 1005 Can't create table `accounting`.`role_user` 
(errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `role_user` add constraint `role_user_role_id_foreign` foreign key (`role_id`) references `roles` (`id`))

Code

This is the code which causes mentioned error:

CreateUsersTable

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }

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

CreateRolesTable

class CreateRolesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('roles', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
    }

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

CreateRoleUser

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateRoleUser extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('role_user', function (Blueprint $table) {
            $table->integer('role_id')->unsigned();
            $table->integer('user_id')->unsigned();
            $table->foreign('role_id')->references('id')->on('roles')->cascade('delete');
            $table->foreign('user_id')->references('id')->on('users')->cascade('delete');
        });
    }

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

Any ideas how to fix this issue?

Answer 1

Make sure the data type matches in both cases. Your primary key data types and foreign key data type must be the same.

Secondly, make sure you put the migration file sequentially. Let's say you have three tables roles, users, and role_user. You want to build a relation between roles with role_user and users with role_user. In the migration directory, the roles and users table(migration file) should come first, then the role_user table(migration file) as the role_user table holds a reference to those two tables. The sequence is maintained by the time of the file created. So you can rename the file changing the time (which is in year_month_date_seconds) format so that it stands before the role_user table. Or you may create a directory for them. Then migrate them separately.

Answer 2

Not helping much, but still shouldn't this be

$table->foreign('role_id')->references('id')->on('roles')->cascade('delete');
$table->foreign('user_id')->references('id')->on('users')->cascade('delete');

replace by

 $table->foreign('role_id')->references('id')->on('roles')->onDelete('cascade');
 $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');