Laravel - migration, table structure modification - correct way

Question

My present table is

Schema::create('students', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->string('first_name', 255);
    $table->string('last_name', 255);
    $table->enum('gender', ['m', 'f']);
    $table->date('date_of_birth');
    $table->integer('roll_number');
    $table->char('section', 1);
    $table->integer('class');
    $table->unsignedBigInteger('school_id');            
    $table->string('photo')->nullable;
    $table->timestamps();

    $table->foreign('school_id')
        ->references('id')->on('schools')
        ->onUpdate('cascade')->onDelete('cascade');

    $table->unique(['roll_number', 'section', 'class', 'school_id']);
});

Standards

Schema::create('standards', function (Blueprint $table) {
       $table->bigIncrements('id');
       $table->string('name');
       $table->unsignedBigInteger('school_id');
       $table->timestamps();

       $table->foreign('school_id')
       ->references('id')->on('schools')
       ->onUpdate('cascade')->onDelete('cascade');
    });

Sections

Schema::create('sections', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->char('name', 1);
    $table->unsignedBigInteger('standard_id');
    $table->timestamps();

    $table->foreign('standard_id')
    ->references('id')->on('standards')
    ->onUpdate('cascade')->onDelete('cascade');
});

now I have standards and sections table and foreign keys from these tables will replace the class and section columns in existing structure and maintain the combination of roll_number, section_id, standard_id and school_id as unique.

I tried

 public function up()
    {
        Schema::table('students', function (Blueprint $table) {
            $table->dropUnique(['roll_number', 'section', 'class', 'school_id']);

            $table->dropColumn('section');
            $table->dropColumn('class');
            $table->unsignedBigInteger('standard_id')->after('roll_number');
            $table->unsignedBigInteger('section_id')->after('standard_id');

            $table->foreign('standard_id')->references('id')->on('standards')
            ->onUpdate('cascade')->onDelete('cascade');
            $table->foreign('section_id')->references('id')->on('sections')
            ->onUpdate('cascade')->onDelete('cascade');

            $table->unique(['roll_number', 'standard_id', 'section_id', 'school_id']); // unique combination
        });
    }

but it seems to be not working.

Error

Illuminate\Database\QueryException : SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (myapp_extra.#sql-2f78_29d, CONSTRAINT students_standard_id_foreign FOREIGN KEY (standard_id) REFERE NCES standards (id) ON DELETE CASCADE ON UPDATE CASCADE) (SQL: alter table students add constraint students_standard_id_foreign foreign key (standard_id) references standards (id) on delete cascade on update cascade)

  at \myapp\vendor\laravel\framework\src\Illuminate\Database\Connection.php:664

Note: standards and sections table are created, before making this migration, so both columns are available.

Answer 1

Why this happens?

This error is occurring because you are inserting a new column without default value and without NULLABLE to your table that already has rows.

When you do it, MySQL will set it value to 0, so all your tables will have standard_id and section_id set to 0, so when you try to add your foreign index, it fails because 0 is not a valid id on your standards/sections table.

So how to fix?

You have some ways to solve this issue:

1st: Setting a default value

If make sense for your application, you can set a default (valid) value to your column, so the foreign key won't fail:

        $table->unsignedBigInteger('standard_id')->default(1)->after('roll_number');
        $table->unsignedBigInteger('section_id')->default(1)->after('standard_id');

Most times it's not so simple, so you will need to define the value dinamically

2nd: Setting the value dynamically

If you have some logic to set the default value for this new columns, you can break you migration into two steps:

// Add the fields first
Schema::table('students', function (Blueprint $table) {
        $table->dropUnique(['roll_number', 'section', 'class', 'school_id']);

        $table->dropColumn('section');
        $table->dropColumn('class');
        $table->unsignedBigInteger('standard_id')->after('roll_number');
        $table->unsignedBigInteger('section_id')->after('standard_id');
}

App\Students::get()->each(function($student) {
    // Apply your logic here
    $student->standard_id = 3;
    $student->section_id = 3;
    $student->save();
});

// Now you can add your foreign keys.
Schema::table('students', function (Blueprint $table) {
        $table->foreign('standard_id')->references('id')->on('standards')
        ->onUpdate('cascade')->onDelete('cascade');
        $table->foreign('section_id')->references('id')->on('sections')
        ->onUpdate('cascade')->onDelete('cascade');

        $table->unique(['roll_number', 'standard_id', 'section_id', 'school_id']); // unique combination
});

3rd: Make the field nullable

If you just don't know or don't have a default value for this fields, so your field should be nullable instead:

    $table->unsignedBigInteger('standard_id')->nullable()->after('roll_number');
    $table->unsignedBigInteger('section_id')->nullable()->after('standard_id');