phplaravellaravel-migrationsmysql-error-1068

Add new auto increment column to existing table


My old migration is:

Schema::create('item_tag', function (Blueprint $table) {

    $table->integer('item_id')->unsigned()->index();
    $table->foreign('item_id')->references('id')->on('items')->onDelete('cascade');

    $table->integer('tag_id')->unsigned()->index();
    $table->foreign('tag_id')->references('id')->on('tags')->onDelete('cascade');

    $table->primary(['item_id', 'tag_id']);

});

Now I want add new auto increment column to this and drop old primary key

I try this:

Schema::table('item_tag', function (Blueprint $table) {

    $table->unsignedInteger('id', true)->first();

    $table->dropPrimary();
    $table->primary('id');

});

But I have a error after migrate:

SQLSTATE[42000]: Syntax error or access violation: 1068 Multiple primary key defined


Solution

  • Drop the primary key in a separate migration and remove $table->primary('id'). Adding an AUTO_INCREMENT column automatically creates a primary key:

    Schema::table('item_tag', function (Blueprint $table) {   
        $table->dropPrimary();
    });
    
    Schema::table('item_tag', function (Blueprint $table) {
        $table->unsignedInteger('id', true)->first();
    });
    

    You can also simplify the second migration:

    Schema::table('item_tag', function (Blueprint $table) {
        $table->increments('id')->first();
    });