Lumen/Laravel - Eloquent: Migration of tables with BIGINT unsigned as PK and FK

Question

I want to migrate a DB containing three tables:

Ad_users

Ad_groups

Ad_usersxad_groups

The latter is obviously a junctiontable, containing just two FKs referencing the PKs of the two other tables. Now, I have the following problem, this is the migration for the ad_users table:

<?php

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

class CreateAdusersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('Ad_users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('common_name');
            $table->string('location');
            $table->string('description');
            $table->integer('postalcode');
            $table->string('physical_delivery_office_name');
            $table->string('telephone_number');
            $table->string('initials');
            $table->string('street_address');
            $table->timestamps();
        });
    }

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

The $table->bigIncrements('id'); creates a Column of the type bigint(20) unsigned. The column in the junction table containing the FK must of course be of the exact same type. However, the Foreign key can of course NOT be the Primary key of the junction table, and therefore I cant use the $table->bigIncrements syntax but instad must use the $table->bigInteger syntax, as seen here in the migration for the Ad_usersxad_groups table:

<?php

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

class CreateAdusersxadgroupsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('Ad_usersxad_groups', function (Blueprint $table) {
            $table->bigInteger('Ad_user_id');
            $table->bigInteger('Ad_group_id');
            $table->foreign('Ad_user_id')->references('id')->on('Ad_users');
            $table->foreign('Ad_group_id')->references('id')->on('Ad_groups');
            $table->timestamps();
        });
    }

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

The problem that arises: $table->bigInteger creates a colum of type bigint(20). It seems that this is not compatible with bigint(20) unsigned type inside the PK column of the original tables. Artisan throws the following error when I try to run the migration:

 SQLSTATE[HY000]: General error: 1005 Can't create table `aetherdb`.`ad_usersxad_groups` (errno: 150 "Foreign key constraint is incorrectly fo
  rmed") (SQL: alter table `Ad_usersxad_groups` add constraint `ad_usersxad_groups_ad_user_id_foreign` foreign key (`Ad_user_id`) references `A
  d_users` (`id`))

Is there any way to deal with this problem apart from just moving away from the bigint(20) unsigned type on the PK columns of the original tables? Can I somehow add the unsigned to the non-primary key columns in the junction table?

Answer 1

You can use $table->bigInteger('Ad_user_id')->unsigned(); to make this FK unsigned.

This function is called Column Modifier. You can check the following link for full list of columns modifiers. Laravel Database Migrations - Columns Modifiers