Laravel migration - how to change the `id` field to be primary and auto-incrementing later

Question

My table 'sales_order_details' has the id field as integer 'int(10)' with a default value 0. I need to change it to be a primary auto-increment key in migration.

I tried in the following way :

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

         $table->integer('id')->default(NULL)->change();
         $table->increments('id')->change();

       });
    }

  public function down()
    {
        Schema::table('sales_order_details', function (Blueprint $table){
        $table->dropPrimary('id')->change();

       });
    }

I get the following errors:

[Illuminate\Database\QueryException] SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table def inition; there can be only one auto column and it must be defined as a key (SQL: ALTER TABLE sales_order_details CHANGE id id INT UNSIGNED AUTO_INCREM ENT NOT NULL)

[Doctrine\DBAL\Driver\PDOException] SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table def inition; there can be only one auto column and it must be defined as a key

[PDOException] SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table def inition; there can be only one auto column and it must be defined as a key

How to make the migration properly ? I am using version 5.4

Why you are writing these both lines $table->integer('id')->default(NULL)->change(); $table->increments('id')->change(); Only second one is required for increments. — Ruchi
Won't default and increments make problem if used together ? — Istiaque Ahmed

Yashvantsinh Zala Yashvantsinh Zala · Accepted Answer · 2017-07-04T11:27:16

You can try like this

  public function up()
    {
        DB::statement("ALTER TABLE sales_order_details MODIFY id INT NOT NULL PRIMARY KEY AUTO_INCREMENT");            
    }

  public function down()
    {
        DB::statement("ALTER TABLE sales_order_details MODIFY id INT NOT NULL");
        DB::statement("ALTER TABLE sales_order_details DROP PRIMARY KEY");
        DB::statement("ALTER TABLE sales_order_details MODIFY id INT NULL");
    }

This is the one of the way you can achieve your goal, i hope this is helpful for you

Laravel migration - how to change the `id` field to be primary and auto-incrementing later

1 Answers