Change primary key in Laravel migration with SQLite

user_7451109 0
Viewed 260

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?

laravel-migrations doctrine-dbal laravel sqlite php
2 Answers

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

user_74511090

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

user_193727390
Related