Laravel migration MySQL fail error 150

Hey, so I am moving my project into production on a normal server but I ran into some issues with migration. I don't have this problem when I am using sqlite for localhost and testing, but for some reason MySQL won't let me migrate my database structure:

In Connection.php line 669:

  SQLSTATE[HY000]: General error: 1005 Can't create table `laravel`.`roles_permissions` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `ro
  les_permissions` add constraint `roles_permissions_permission_id_foreign` foreign key (`permission_id`) references `permissions` (`id`) on delete cascade)


In Connection.php line 463:

  SQLSTATE[HY000]: General error: 1005 Can't create table `laravel`.`roles_permissions` (errno: 150 "Foreign key constraint is incorrectly formed")

Here is my code from migration file:

public function up()
    {
        Schema::create('roles_permissions', function (Blueprint $table) {
            $table->unsignedBigInteger('role_id');
            $table->unsignedBigInteger('permission_id');

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

            $table->primary(['role_id','permission_id']);
        });
    }

EmilMoe

5 years ago

Level 10

Are the primaries also BigIntegers?

iKyzu

5 years ago

Level 1

What you mean with primaries?

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

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

sr57

5 years ago

Level 39

Primaries mean the primary keys (index), which you need to define constraint
The pb is with the table permission & roles_permissions (not users)

iKyzu

5 years ago

Best Answer

Level 1

I was able to fix the problem with changing the name of migration of "permissions" so it's above "roles_permissions" an that seems to help with the problem I had.

Please or to participate in this conversation.