Change primary key in Laravel migration with SQLite-CodePudding

I have the following migration:

Schema::create('items', function(Blueprint $table) {
    $table->uuid('id')->primary();
    // more columns ...
});

Now, we want to add an additional auto-increment column:

Schema::table('items', function(Blueprint $table) {
    $table->dropPrimary('id');
    $table->rename('id', 'SystemId')->change();
    $table->id();
});

Problem: SQLite doesn't allow changing the primary key
Solution: It's recommended to delete the table and create it with the changed schema

Of course, that works in theory but it is anything but DRY to copy the code from our first migration to our second. So my question is: Is there another way to achieve this?

CodePudding user response：

You can't modify SQLite tables in any significant way after they have been created. As you said the accepted suggested solution is to create a new table with the correct requirements and copy your data into it, then drop the old table. That's the only way to do this.

Official documentation about this: http://sqlite.org/faq.html#q11

CodePudding user response：

So, I finally came up with a solution that is generic enough to be reusable. Would be great to be included into Laravel, but a package is probably more likely.

use Doctrine\DBAL\Schema\Table;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

class ExtendedSQLiteAlterTableMigration extends Migration
{
    public function extendedAlterTable(string $name, callable $callback)
    {
        /** @var \Doctrine\DBAL\Schema\AbstractSchemaManager */
        $schemaManager = DB::connection()->getDoctrineSchemaManager();
        $tableName = 'test';
        /** @var \Doctrine\DBAL\Schema\Table */
        $table = $this->getTempTable($schemaManager, $tableName);
        call_user_func($callback, $table);
        $tempName = $table->getName();
        //$schemaManager->renameTable($tableName, $tempName);
        $schemaManager->createTable($table);
        $schemaManager->dropTable($tableName);
        $schemaManager->renameTable($tempName, $tableName);
    }
}

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

class AddAutoIncrementPrimaryKeyToTestTable extends ExtendedSQLiteAlterTableMigration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        $this->extendedAlterTable('test', function(Table $table) {$table->dropPrimaryKey();
            $table->addColumn('id', 'bigint', [
                'autoincrement' => true,
            ]);
            $table->setPrimaryKey([ 'id' ]);
        });
    }
}