Many to Many on Laravel migration

user_4687904 0
Viewed 19414

I have 3 tables to connect each other. Tables name's roles, role_user, and users. I want to make migration on laravel and adding some constraint. and here's what in my role tables migration:

    Schema::create('roles', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->string('description');
        $table->timestamps();
    });

and here's my Users table migration:

Schema::create('users', function (Blueprint $table) {
    $table->increments('id');
    $table->string('username')->unique();
    $table->string('name');
    $table->string('email')->unique();
    $table->string('password');
    $table->boolean('active')->default(0);
    $table->softDeletes();
    $table->rememberToken();
    $table->timestamps();
});

and here's my role_user table migration:

    Schema::create('role_user', function (Blueprint $table) {
        $table->integer('role_id')->unsigned();
        $table->integer('user_id')->unsigned();

        $table->unique(['role_id', 'user_id']);
        $table->foreign('role_id')->references('id')->on('roles')
            ->onDelete('cascade')->onUpdate('cascade');
        $table->foreign('user_id')->references('id')->on('users')
            ->onDelete('cascade')->onUpdate('cascade');
    });

in my Migration order i put roles table on top of users already but i got this kind of errors:

  [Illuminate\Database\QueryException]
  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `role_user` add constraint `role_user_role_id_foreign` foreign key (`role_id
  `) references `roles` (`id`) on delete cascade on update cascade)



  [PDOException]
  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint
laravel-5 laravel migration
4 Answers

Probably your problem was with the order of creation of migrations.

Laravel runs the migrations in order of creation using the timestamp in the file name , because of that, if you created them in the following order:

  1. roles in 2018_06_02_023539_create_roles_table
  2. role_user in 2018_06_02_023800_create_role_user_table
  3. users in 2018_06_02_023815_create_users_table

The table users would not exist when referenced in the table role_user, thus causing an SQL error. The most simple fix that you could do is renaming the role_user migration file in this way:

2018_06_02_023800_create_role_user_table => 2018_06_02_023820_create_role_user_table

user_103326210

I delete all my table and left just 4 tables :

create_user_table,
create_password_reset, 
create_roles_table, 
create_role_user

and everything works fine.

Till now I don't understand what makes these errors although I already put the same orders.

user_46879040

Try this one on the pivot table

    public function up()
{
    Schema::create('role_user', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->unsignedBigInteger('role_id');
        $table->unsignedBigInteger('user_id');
        $table->timestamps();
        $table->foreign('role_id')->references('id')->on('roles')->onDelete('cascade');
        $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
    });
}
user_117391890

in table "role_user" replace columns type from integer to bigIncrements

user_71696590
Related