In my laravel appication I have two migrations.

2022_06_08_075452_create_schedule_types_table.php

<?php

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

class CreateScheduleTypesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('schedule_types', function (Blueprint $table) {
            $table->increments('id');
            $table->string('schedule_type_name');
            $table->string('schedule_type_color');
            $table->string('schedule_type_description');
            $table->boolean('status')->default(0);
            $table->timestamps();
            $table->softDeletes();
        });
    }

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

2022_06_08_054916_create_schedules_table.php

<?php

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

class CreateSchedulesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('schedules', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('department_id');
            $table->foreign('department_id')->references('id')->on('departments');
            $table->unsignedBigInteger('user_id');
            $table->foreign('user_id')->references('id')->on('users');
            $table->unsignedBigInteger('company_id');
            $table->foreign('company_id')->references('id')->on('companies');
            $table->unsignedBigInteger('added_by');
            $table->foreign('added_by')->references('id')->on('users');
            $table->string('schedule_name');
            $table->integer('schedule_type_id');
            $table->foreign('schedule_type_id')->references('id')->on('schedule_types');
            $table->date('schedule_start_date')->nullable();
            $table->date('schedule_end_date')->nullable();
            $table->date('schedule_actual_end_date')->nullable();
            $table->time('schedule_travel_time')->nullable();
            $table->unsignedBigInteger('rotation_scheme_id');
            $table->foreign('rotation_scheme_id')->references('id')->on('schedule_rotational');
            $table->date('rotational_schedule_period_from')->nullable();
            $table->date('rotational_schedule_period_to')->nullable();
            $table->unsignedBigInteger('rotation_shift_id');
            $table->foreign('rotation_shift_id')->references('id')->on('schedule_has_shift');
            $table->timestamps();
            $table->softDeletes();

            $table->index([
                'department_id', 'user_id', 'schedule_type_id', 'company_id', 'added_by','schedule_start_date',
                'schedule_end_date', 'rotation_scheme_id', 'rotational_schedule_period_from', 'rotational_schedule_period_to',
                'rotation_shift_id']);
        });
    }

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

But, wen I tried to run my migrations, I'm getting following error,

SQLSTATE[HY000]: General error: 1005 Can't create table hrapp_dev.schedules (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table schedules add constraint schedules_schedule_type_id_foreign foreign key (schedule_type_id) references schedule_types (id))

I initially thought this is occurring due to an int and bigint issue, but both are in int....

UPDATE

I ran the two migration files one at a time due to timestamp issue, but still getting the same issue...

CodePudding user response：

Your foreign key schedule_type_id should be unsigned for the relationship to work. In your 2022_06_08_054916_create_schedules_table.php change to:

$table->integer('schedule_type_id')->unsigned();

CodePudding user response：

Do it this way following the more cleaner syntax from Laravel 7.x:

class CreateSchedulesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('schedules', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->foreignId('department_id')->constrained();
            $table->foreignId('user_id')->constrained();
            $table->foreignId('company_id')->constrained();
            $table->foreignOd('added_by')->constrained('users');
            $table->string('schedule_name');

            // Here is your problem the schedule_type_id was not correctly formed
            $table->foreignId('schedule_type_id')->constrained();
            $table->date('schedule_start_date')->nullable();
            $table->date('schedule_end_date')->nullable();
            $table->date('schedule_actual_end_date')->nullable();
            $table->time('schedule_travel_time')->nullable();
            $table->foreignId('rotation_scheme_id')->constrained('schedule_rotational');
            $table->date('rotational_schedule_period_from')->nullable();
            $table->date('rotational_schedule_period_to')->nullable();
            $table->foreignId('rotation_shift_id')->constrained('schedule_has_shift');
            $table->timestamps();
            $table->softDeletes();

            $table->index([
                'department_id', 'user_id', 'schedule_type_id', 'company_id', 'added_by','schedule_start_date',
                'schedule_end_date', 'rotation_scheme_id', 'rotational_schedule_period_from', 'rotational_schedule_period_to',
                'rotation_shift_id']);
        });
    }

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

the foreignId('tablename_id`) creates a foreign key and constrained() will make it constrained using the column name to determine the table. If you have a different table name than the one used in column name then you can pass the table in constrained('table')