Add foreign key to a migration (Laravel)

Question

I´ve spent the whole day trying to figure out what is happening with the following migration.

This the migration of the table. My idea is to use 'id_stays_abroad' and 'user_id' as foreign keys.

    class CreateBookingStaysAbroad extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('booking__stays__abroads', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->timestamps();
            $table->unsignedInteger('id_stays_abroad')->nullable();
            $table->unsignedInteger('user_id')->nullable();
            $table->string('status')->nullable();
            $table->date('departure_date');
            $table->date('return_date');
            $table->integer('number_weeks');

        });

    }

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

This is the other migration to add the foreign keys

   class AddForeignKeyBookingStaysAbroadTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('booking__stays__abroads', function($table) {

            $table->foreign('id_stays_abroad')
                    ->references('id')
                    ->on('stays_abroads');

            $table->foreign('user_id')
                    ->references('id')
                    ->on('users');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
            Schema::table('booking__stays__abroads', function (Blueprint $table) {
            $table->dropForeign('booking__stays__abroads_id_stays_abroad_foreign');
            $table->dropForeign('booking__stays__abroads_user_id_foreign');

    });
  }
}

When I run php artisan migrate I got the following error:

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table booking__stays__abroads add constraint booking__stays__abroads_id_stays_abroad_foreign foreign key (id_stays_abroad) references stays_abroads (id))

Thanks in advance, guys.

Answer 1

First of all, the attribute should be named stays_abroad_id and not id_stays_abroad.

The issue is that the size and nature of stays_abroads.id and stays_abroid_id should match (unsigned big integer in this case).

Also, you dont need an ID in a pivot table. You will not be declaring a model for it in laravel.

The name of the table should reflect the two sides of the pivot table, so name it stays_abroad_user (singular and alphabetic order separated by an underscore)

class CreateBookingStaysAbroad extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('stays_abroad_user', function (Blueprint $table) {
            $table->unsignedBigInteger('stays_abroad_id')->nullable();
            $table->unsignedBigInteger('user_id')->nullable();
            $table->string('status')->nullable();
            $table->date('departure_date');
            $table->date('return_date');
            $table->integer('number_weeks');
            $table->timestamps();
            $table->foreign('stays_abroad_id')
                    ->references('id')
                    ->on('stays_abroads');

            $table->foreign('user_id')
                    ->references('id')
                    ->on('users');
        });

    }

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

Answer 2

You are not using the same column definition for id and id_stays_abroad. Use unsignedBigInteger instead of unsignedInteger.

$table->unsignedBigInteger('id_stays_abroad')->nullable();
$table->unsignedBigInteger('user_id')->nullable();

Answer 3

I'm also quite new to Laravel but I think you might need to change these two data types:

$table->unsignedInteger('id_stays_abroad')->nullable();
$table->unsignedInteger('user_id')->nullable();

to

$table->unsignedBigInteger('id_stays_abroad')->nullable();
$table->unsignedBigInteger('user_id')->nullable();

Worth a try!

Answer 4

Try this make this i think your id in users table is bigIncrements.so for foreignkey use this

$table->unsignedBigInteger('id_stays_abroad')->nullable();
$table->unsignedBigInteger('user_id')->nullable();

DB::statement('SET FOREIGN_KEY_CHECKS=0;');
Schema::table('booking__stays__abroads', function($table) {

            $table->foreign('id_stays_abroad')
                    ->references('id')
                    ->on('stays_abroads');

            $table->foreign('user_id')
                    ->references('id')
                    ->on('users');
        });
DB::statement('SET FOREIGN_KEY_CHECKS=1;');

DB::statement('SET FOREIGN_KEY_CHECKS=0;');
Schema::table('booking__stays__abroads', function (Blueprint $table) {
            $table->dropForeign('booking__stays__abroads_id_stays_abroad_foreign');
            $table->dropForeign('booking__stays__abroads_user_id_foreign');

DB::statement('SET FOREIGN_KEY_CHECKS=1;');