Change primary key in Laravel migration with SQLite

Question

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?

Answer 1

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 $tableName, callable $callback)
    {
        /** @var \Doctrine\DBAL\Schema\AbstractSchemaManager */
        $schemaManager = DB::connection()->getDoctrineSchemaManager();
        /** @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);
    }

    private function getTempTable($schemaManager, string $name)
    {        
        $columns     = $schemaManager->listTableColumns($name);
        $foreignKeys = [];

        //if ($this->_platform->supportsForeignKeyConstraints()) {
            $foreignKeys = $schemaManager->listTableForeignKeys($name);
        //}

        $indexes = $schemaManager->listTableIndexes($name);

        return new Table("temp_$name", $columns, $indexes, [], $foreignKeys);
    }
}

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' ]);
        });
    }
}

This follows the instructions on the SQLite website

Answer 2

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