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?

shaedrich · Accepted Answer · 2022-06-24T07:46:58.487

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

score 0 · Answer 2 · answered Jun 23 '22 at 07:54

0

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

answered Jun 23 '22 at 07:54

Laralex

125
8

Thanks for your answer. I am aware of how I would do that in raw SQL. My problem is, how to achieve that in Laravel in a non-repetetive way. – shaedrich Jun 23 '22 at 08:25
Here's how I would personally do this: Copy the contents of the old migration and paste it into a new migration. Change it as you wish and then just delete the old migration. Then continue with exporting the data from your current table and then delete it. Go to your `migrations` table in your database and delete the migration from there so Laravel knows it hasn't migrated. Then run `php artisan migrate` and import your data after replacing` id` with `SystemId` in the SQL file. Declare your new primary key in your model `protected $ primaryKey = 'SystemId';` and I guess you're done. – Laralex Jun 23 '22 at 08:36
Sounds not like an approach that is exactly foolproof since there are a lot of manual steps involved. Also, take into consideration that this migrations can also already have been run by other people who would have to make these changes themselves accordingly. Also, if there are multiple migrations for that table, you'd have to do these steps for each migration separately. – shaedrich Jun 23 '22 at 08:41
The database grammar and platform implementations use `TableDiff` so that it can partly be automated. It just looks to me that I still have to write the rest myself or did I miss any method that actually already automates deleting the old and creating the new table all in one step? – shaedrich Jun 23 '22 at 08:43
To be honest I'm a little confused what your problem actually is. There is no command that automates the process of deleting the old table, creating the new table and import your data into the new table. Unfortunately, you will have to accept it. :( – Laralex Jun 23 '22 at 08:55
Well, I was asking because, some of the `TableDiff` stuff is actually already done for SQLite. So I was wondering why it stopped halfway through and Laravel just ignores method calls that are not supported by the database driver implementation without any notice. – shaedrich Jun 23 '22 at 11:09
I posted an answer with what I did. I hoped, it'd be more DRY but at least, there's some things are more DRY than doing it all manually. – shaedrich Jun 23 '22 at 13:19

Change primary key in Laravel migration with SQLite

2 Answers2