Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Unable to change primary key in a single migration for Sql Server #3736

Open
akrabat opened this issue Nov 15, 2019 · 0 comments
Open

Unable to change primary key in a single migration for Sql Server #3736

akrabat opened this issue Nov 15, 2019 · 0 comments
Labels

Comments

@akrabat
Copy link

@akrabat akrabat commented Nov 15, 2019

Bug Report

Q A
BC Break ?
DBAL Version v2.10.0
Migrations Version 2.2.0

Summary

With Sql Server, you cannot drop a table's primary key and set a new one in the same migration. Using two consecutive migrations works though.

This was reported as issue 871 on doctrine/migrations and I was told to raise it here.

Current behaviour / How to reproduce

Given this 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']);
    }
}

Running the Migrations migrate command gives 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.

Note that the name of the index it is trying to drop is called [primary] when it should be [PK__page_cat__E48D0CA0589C25F3].

Expected behavior

Changing to two consecutive migrations works:

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();
    }
}

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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.