[FIXED] How to change a column type from integer to biginteger while other tables have foreign id relation in laravel?

Issue

I am trying to change a column (admin_campaigns table) from integer to big integer by adding this:

 $table->bigInteger('category_id')->change();

But it says, Cannot change column 'category_id': used in a foreign key constraint 'admin_campaigns_ category_id_foreign'")

How can I solve this?

admin_campaigns table:

 public function up()
    {
        Schema::create('admin_campaigns', function (Blueprint $table) {

            $table->bigIncrements('id');

            $table->string('title',50);
            $table->integer('category_id')->unsigned();
            $table->foreign('category_id')
                ->references('id')->on('admin_campaign_categories')
                ->onDelete('cascade');

            $table->tinyInteger('is_used_multimedia')->default(1)->comment('1 = yes and 0 = no');

            $table->timestamps();


        });
    }

New migration:

 public function up()
    {
        Schema::table('admin_campaigns', function (Blueprint $table) {
            $table->bigInteger('category_id')->change();
        });
    }

Solution

To change the category_id from int to bigint, you would need to:

1. Drop the foreign key on the category_id column: $table->dropForeign('admin_campaigns_category_id_foreign');

2. Change the category_id column to bigint: $table->bigInteger('category_id')->change();

3. Before being able to re-establish the foreign key, you need to change the admin_campaign_categories.id column to bigint too:

     Schema::table('admin_campaign_categories', function (Blueprint $table) {
        $table->bigInteger('id')->change();
     });
   

Before you can do that, you obviously need to do steps 1 and 2 on all other columns in all other tables that have foreign keys on admin_campaign_categories.id.

So if the admin_campaigns table was the only table referencing admin_campaign_categories.id, you can do:

public function up()
{
    Schema::table('admin_campaigns', function (Blueprint $table) {
        $table->dropForeign('admin_campaigns_category_id_foreign');
    });

    Schema::table('admin_campaign_categories', function (Blueprint $table) {
       $table->bigInteger('id')->change();
    });

    Schema::table('admin_campaigns', function (Blueprint $table) {
        $table->bigInteger('category_id')->change();
        $table->foreign('category_id')
            ->references('id')->on('admin_campaign_categories')
            ->onDelete('cascade');
    });
}

public function down()
{
    Schema::table('admin_campaigns', function (Blueprint $table) {
        $table->dropForeign('admin_campaigns_category_id_foreign');
    });

    Schema::table('admin_campaign_categories', function (Blueprint $table) {
        $table->integer('id')->change();
    });

    Schema::table('admin_campaigns', function (Blueprint $table) {
        $table->integer('category_id')->change();
        $table->foreign('category_id')
              ->references('id')->on('admin_campaign_categories')
              ->onDelete('cascade');
    });
}

---

A Word on Primary Key Columns

I also want to emphasize that you should never use signed integer / signed big integer types for numeric primary key columns. Instead, you should use

$table->unsignedBigInteger('id')->change();

everywhere in the migration above.

The reason for this is that a numeric primary key will probably never be set to a negative value, which holds true especially when using it as an auto increment column.

By setting the column to integer or bigInteger types, you limit the maximum assignable value to 2,147,483,647 or 2⁶³-1, respectively, whereas you could save a value twice as high when using unsignedInteger or unsignedBigInteger types, with the same amount of storage needed (4/8 bytes).

In most scenarios, both ranges are so high that you will probably never notice a difference, but it's basically a waste of resources. I have seen an integer id exceed the maximum range once in a user table for a very busy application, and it's not pretty. Basically, in MySQL the whole DB just stops working completely.

Answered By - Lupinity Labs

Answer Checked By - Clifford M. (PHPFixing Volunteer)