Changing an SQL Server primary key in Doctrine Migrations

I recently came across a rather weird quirk when trying to change a primary key in Sql Server using Doctrine Migrations: you need to use two migrations to get it to work.

This is incredibly minor and I’m only writing it up as it confused me for a while so I thought that I’d document so that I’ll find this article if I run across it again in the future!

This is the migration:

final class Version20191023125629 extends AbstractMigration
{
    public function up(Schema $schema) : void
    {
        $table = $schema->getTable('page_category');
        $table->dropPrimaryKey();
        $table->setPrimaryKey(['page_uuid', 'category_id']);
    }

    public function down(Schema $schema) : void
    {
        $table = $schema->getTable('page_category');
        $table->dropPrimaryKey();
        $table->setPrimaryKey(['page_id', 'category_id']);
    }
}

When you run it with SQL Server, you get this error:

 ++ migrating 20191023125629

     -> IF EXISTS (SELECT * FROM sysobjects WHERE name = '[primary]')
    ALTER TABLE page_category DROP CONSTRAINT [primary]
ELSE
    DROP INDEX [primary] ON page_category
Migration 20191023125629 failed during Execution. Error An exception occurred while executing 'IF EXISTS (SELECT * FROM sysobjects WHERE name = '[primary]')
    ALTER TABLE page_category DROP CONSTRAINT [primary]
ELSE
    DROP INDEX [primary] ON page_category':

SQLSTATE[42S02]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot drop the index 'page_category.primary', because it does not exist or you do not have permission.

The actual problem is that the primary key name of [primary] is incorrect. Something somewhere is losing the name of the current primary key ([PK__page_cat__E48D0CA0589C25F3]) because there’s a setPrimaryKey() call in the same migration.

If we split into two migrations:

final class Version20191023125629 extends AbstractMigration
{
    public function up(Schema $schema) : void
    {
        $table = $schema->getTable('page_category');
        $table->dropPrimaryKey();
    }

    public function down(Schema $schema) : void
    {
        $table = $schema->getTable('page_category');
        $table->setPrimaryKey(['page_id', 'category_id']);
    }
}

and

final class Version20191023125630 extends AbstractMigration
{
    public function up(Schema $schema) : void
    {
        $table = $schema->getTable('page_category');
        $table->setPrimaryKey(['page_uuid', 'category_id']);
    }

    public function down(Schema $schema) : void
    {
        $table = $schema->getTable('page_category');
        $table->dropPrimaryKey();
    }
}

Then it works as expected:

  ++ migrating 20191023125629

     -> IF EXISTS (SELECT * FROM sysobjects WHERE name = 'PK__page_cat__E48D0CA0589C25F3')
    ALTER TABLE page_category DROP CONSTRAINT PK__page_cat__E48D0CA0589C25F3
ELSE
    DROP INDEX PK__page_cat__E48D0CA0589C25F3 ON page_category

  ++ migrated (0.77s)

  ++ migrating 20191023125630

     -> ALTER TABLE page_category ADD PRIMARY KEY (page_uuid, choice_key_name)

  ++ migrated (0.77s)

As you can see, it’s hardly a big problem to create two migrations to work around this and I’ve reported it to the project as issue 3736.