Cannot change primary key to multi-column one with SQLite

[InvisibleSmiley]

When using the SQLite adapter, tables can be created with a multi-column primary key.

Trying to change the primary key to a multi-column one afterwards fails (whether it previously was a single-column or multi-column one) with InvalidArgumentException "Invalid value for primary key: [...]".

[MasterOdin]

Could you provide an example migration?

[InvisibleSmiley]

<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class Foo extends AbstractMigration
{
    public function change(): void
    {
        $this->create();
        $this->modify();
    }

    private function create(): void
    {
        $this->table('foo', ['id' => false, 'primary_key' => ['foo_id1', 'foo_id2']])
            ->addColumn('foo_id1', 'integer')
            ->addColumn('foo_id2', 'integer')
            ->create();
    }

    private function modify(): void
    {
        $this->table('foo')->addColumn('foo_bar', 'string')->save();
        $this->table('foo')->changePrimaryKey(['foo_bar', 'foo_id1', 'foo_id2'])->save();
    }
}

[ArchangelDesign]

I'm experiencing the same problem although in my case I'm trying to remove composite key and replace it with single column.

    public function change()
    {
        $this->table('orders')
            ->changePrimaryKey('id')
            ->changeColumn('id', 'integer', ['identity' => true])
            ->update();
    }

[meinemitternacht]

I am also experiencing this issue. It looks like getChangePrimaryKeyInstructions() in SQLiteAdapter does not support multiple columns: https://github.com/cakephp/phinx/blob/0.x/src/Phinx/Db/Adapter/SQLiteAdapter.php#L521

[MasterOdin]

I'm experiencing the same problem although in my case I'm trying to remove composite key and replace it with single column.

    public function change()
    {
        $this->table('orders')
            ->changePrimaryKey('id')
            ->changeColumn('id', 'integer', ['identity' => true])
            ->update();
    }

The error looks like it's arising in that it attempts to apply the changeColumn action before the changePrimaryKey action, and that leads to the error. Doing something like this:

        $this->table('foo')
            ->changePrimaryKey('id')
            ->update();

        $this->table('foo')
            ->changeColumn('id', 'integer', ['identity' => true])
            ->update();

works though.

I'm not sure if there'd be issues if were to flip the order of action executions, would have to think about it, as that would affect all adapters, not just sqlite.

[meinemitternacht]

@MasterOdin Are there any plans to support composite keys with the SQLite adapter? In the meantime, I am just using plain SQL migrations with up()/down().

[MasterOdin]

Yes, the plan is to support composite keys with sqlite at some point. Right now, what I plan to work on for this is:

1. Improve FK pragma handling to be a bit more consistent (Rework sqlite fk pragma handling #2365)
2. Fix setting primary key and identity in the same chain (Cannot change primary key to multi-column one with SQLite #2048 (comment))
3. Make it so that setting primary key does not also add auto_increment implicitly for integer columns (SQLiteAdapter adds autoincrement to integer column when changing primary key #2364)
4. Fix changing table to composite primary key