Laravel migration file 2 foreign keys can not be created

I am trying to create a new table where 2 foreign key will be set

 public function up()
    {
        Schema::create('role_permissions_link', function (Blueprint $table) {
            $table->id();
            $table->integer('permissions_id')->unsigned();
            $table->integer('users_role_id')->unsigned();
            $table->timestamps();
            $table->index(['permissions_id', 'users_role_id']);
        });

        Schema::table('role_permissions_link', function (Blueprint $table) {
            $table->foreign('permissions_id')->references('id')->on('permissions');
            $table->foreign('users_role_id')->references('id')->on('users_role');
        });
    }

I keep getting an error saying

SQLSTATE[HY000]: General error: 1005 Can't create table `homestead`.`ja_role_permissions_link` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `ja_role_permissions_link` add constraint `ja_role_permissions_link_permissions_id_foreign` foreign key (`permissions_id`) references `ja_permissions` (`id`))

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:669
    665|         // If an exception occurs when attempting to run a query, we'll format the error
    666|         // message to include the bindings with SQL, which will make this exception a
    667|         // lot more helpful to the developer instead of just the database's errors.
    668|         catch (Exception $e) {
  > 669|             throw new QueryException(
    670|                 $query, $this->prepareBindings($bindings), $e
    671|             );
    672|         }
    673| 

      +9 vendor frames 
  10  database/migrations/2020_04_06_144324_create_role_permissions_link_table.php:27
      IlluminateSupportFacadesFacade::__callStatic()

      +34 vendor frames 
  45  artisan:37
      IlluminateFoundationConsoleKernel::handle()

I have tried moving the code around and some online solutions but keep getting the same error.

permission migration file

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

users_role migration file

public function up()
{
    Schema::create('users_role', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->timestamps();
        $table->index(['id']);
    });
}

Answer

Solution 1 – Data Types ~(Not this time!)~~

Something to check would be that the datatypes of your columns and keys match.

It is not uncommon to see this error if the foreign key (i.e. $table->integer('permissions_id')->unsigned();) on one table does not match the id on the other table ($table->bigIncrements('id');)

Solution 2 – Migration Order (Correct)

permissions should be before role_permissions. You may need to rename the migrations slightly as they will run in date order based off of their names.

Advertisement

Answer