Failure to create composite primary key on upgrade to laravel framework 11.15.0

[midnite81]

Laravel Version

11.15.0

PHP Version

8.3.9

Database Driver & Version

8.0.37 MySql

Description

We have two migrations which in laravel/framework 11.14.0 run without issue, however in laravel/framework 11.15.0+ now return us the following error:

Illuminate\Database\QueryException: SQLSTATE[42000]: Syntax error or access violation: 
1075 Incorrect table definition; there can be only one auto column and it must be defined 
as a key (Connection: tenant, SQL: alter table `ticket_users` drop primary key)

The migrations concerned are as follows:

Create the table

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration 
{
    /**
     * Run the migrations.
     */
    public function up()
    {
        Schema::create('ticket_users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('ticket_id');
            $table->unsignedBigInteger('user_id');
            $table->unique(['ticket_id', 'user_id']);
            $table->foreign('ticket_id')->references('id')->on('tickets')->onDelete('cascade');
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
        });
    }
};

Update the table

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration 
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::table(
            'ticket_users',
            function (Blueprint $table) {
                $table->dropPrimary();
                $table->bigInteger('id')->change();
                $table->dropColumn('id');
            }
        );
        Schema::table(
            'ticket_users',
            function (Blueprint $table) {
                $table->primary(['ticket_id', 'user_id']);
            }
        );
    }
};

Steps To Reproduce

If you run this migration first

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration 
{
    /**
     * Run the migrations.
     */
    public function up()
    {
        Schema::create('tickets', function (Blueprint $table) {
            $table->bigIncrements('id');
        });

        Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
        });
    }
};

then run the two migrations in the issue described above, you'll get a MySql error

Illuminate\Database\QueryException: SQLSTATE[42000]: Syntax error or access violation: 
1075 Incorrect table definition; there can be only one auto column and it must be defined 
as a key (Connection: tenant, SQL: alter table `ticket_users` drop primary key)

If you do the same in 11.14.0, then it will run without issue.

I suspect it could be an issue which has arisen out of #51373

[hafezdivandari]

@midnite81 on MySQL you can't drop the key if the column is auto incremental, so you should first drop the auto increment then drop its primary (just switch those 2 commands):

Schema::table('ticket_users', function (Blueprint $table) {
    $table->bigInteger('id')->change(); // drop auto increment first
    $table->dropPrimary();              // then drop primary
    $table->dropColumn('id');
    $table->primary(['ticket_id', 'user_id']);
});

Before 11.15, the change commands would always run at first, that's why you were not getting any error, but after 11.15, the commands compile in the real order.

However, dropping auto increment and primary seems redundant in your case, as you are eventually dropping the whole column, you may want to simplify this to:

Schema::table('ticket_users', function (Blueprint $table) {
    // $table->bigInteger('id')->change(); // no need for this
    // $table->dropPrimary();              // no need for this either
    $table->dropColumn('id');
    $table->primary(['ticket_id', 'user_id']);
});

[midnite81]

Thanks @hafezdivandari. I'll give that a go when I'm back at my desk.

[midnite81]

Hey @hafezdivandari - that seems to fix my issue. Thank you for your helpful response. 👍🏻