Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel migration failing when changing primary key: 1068 Multiple primary key defined

I have a table workflows with an auto-incrementing primary key called 'click_id'. I would like to add a field ‘id’ at the beginning of the table and make that the auto incrementing primary key.

This is the migration that fails:

Schema::table('workflows', function (Blueprint $table) {
    $table->unsignedBigInteger('click_id')->change();
    $table->dropPrimary('click_id');
    $table->bigIncrements('id')->first();
    $table->unique(['click_id','review_id']);
});

The error is

SQLSTATE[42000]: Syntax error or access violation: 1068 Multiple primary key defined (Connection: mysql, SQL: alter table `workflows` add `id` bigint unsigned not null auto_increment primary key first)

This suggests that the dropPrimary() command is not completing before laravel continues onto the next line of the migration.

The only workaround I’ve found to be sucessful is to split it into two separate chunks within the up() method.

public function up(): void
    {
        Schema::table('workflows', function (Blueprint $table) {
            $table->unsignedBigInteger('click_id')->change();
            $table->dropPrimary('click_id');
        });

        Schema::table('workflows', function (Blueprint $table) {
            $table->bigIncrements('id')->first();
            $table->unique(['click_id','review_id']);
        });
    }

  
public function down(): void
    {
        Schema::table('workflows', function (Blueprint $table) {
            $table->dropUnique('workflows_click_id_review_id_unique');
            $table->unsignedBigInteger('id')->change();
            $table->dropPrimary('id');
            $table->dropColumn('id');
        });
        
        DB::statement('ALTER TABLE `workflows` CHANGE `click_id` `click_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, add PRIMARY KEY (`click_id`)');
    }

Why does it not work as expected when within a single closure?

System: Laravel 10, MariaDB 10.4.32

Tags:

2

Answers


  1. 0

    Starting with this migration:

    Schema::table('test', function (Blueprint $table) {
    $table->unsignedBigInteger('click_id')->change();
    $table->dropPrimary('click_id');
    $table->bigIncrements('id')->first();
    $table->unique(['click_id']);
});

    This is the output that you get when dd’ing inside the build method of the Blueprint class (here)

    2024_04_04_133407_test_change_index array:4 [
  0 => "alter table `test` modify `click_id` bigint unsigned not null"
  1 => "alter table `test` add `id` bigint unsigned not null auto_increment primary key first"
  2 => "alter table `test` drop primary key"
  3 => "alter table `test` add unique `test_click_id_unique`(`click_id`)"
]

    As you can see, this does NOT follow the order of our instructions! The dropping of the primary key and the index queries get pushed to the end of the array.

    Further research is needed to understand why they made this choice.

    If you keep following the instruction pointer, you end up here:

    protected function addImpliedCommands(Connection $connection, Grammar $grammar)
{
    if (count($this->getAddedColumns()) > 0 && ! $this->creating()) {
        array_unshift($this->commands, $this->createCommand('add'));
    }

    if (count($this->getChangedColumns()) > 0 && ! $this->creating()) {
        array_unshift($this->commands, $this->createCommand('change'));
    }

    $this->addFluentIndexes();

    $this->addFluentCommands($connection, $grammar);
}

    Even without over-analyzing each of these calls, it seems the laravel chooses a specific order when it compiles the list of queries to run. That makes it necessary to separate the changes into 2 Schema::table(...) calls.

    Login or Signup to reply.
  2. 0

    It seems like you’re trying to change the primary key of the workflows table in your Laravel migration.there are a couple of issues in your migration code that might be causing the problem:

    1. In the up() method, you’re trying to drop the primary key using $table->dropPrimary(‘click_id’), but you’re passing the column name instead of the primary key constraint name. Laravel expects the name of the primary key constraint, not the column name. If you haven’t explicitly named your primary key constraint, Laravel generates a default name for it. To fix this, you can simply use $table->dropPrimary() without specifying the column name.
    2. In the down() method, you’re trying to drop the primary key using $table->dropPrimary(‘id’) and then immediately dropping the column id using $table->dropColumn(‘id’). Dropping a column that’s part of the primary key would automatically remove the primary key constraint as well. So, there’s no need to explicitly drop the primary key constraint again.

    Here are the corrected code for your migration:

    public function up(): void
{
    Schema::table('workflows', function (Blueprint $table) {
        $table->unsignedBigInteger('click_id')->change();
        $table->dropPrimary(); // Drop the existing primary key
    });

    Schema::table('workflows', function (Blueprint $table) {
        $table->bigIncrements('id')->first();
        $table->unique(['click_id','review_id']);
    });
}

public function down(): void
{
    Schema::table('workflows', function (Blueprint $table) {
        $table->dropUnique('workflows_click_id_review_id_unique');
        $table->unsignedBigInteger('id')->change();
        $table->dropColumn('id'); // Dropping the column will automatically remove the primary key constraint
    });
    
    // Re-add the primary key constraint
    DB::statement('ALTER TABLE `workflows` CHANGE `click_id` `click_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, add PRIMARY KEY (`click_id`)');
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search