21

Changing an SQL Server primary key in Doctrine Migrations - Rob Allen

 4 years ago
source link: https://akrabat.com/changing-an-sql-server-primary-key-in-doctrine-migrations/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

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 .


About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK